<?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.ScEmbryoFlushMapper">

    <resultMap type="ScEmbryoFlush" id="ScEmbryoFlushResult">
        <result property="id"    column="id"    />
        <result property="flushTime"    column="flush_time"    />
        <result property="donorFemaleNo"    column="donor_female_no"    />
        <result property="donorFemaleVariety"    column="donor_female_variety"    />
        <result property="donorMaleNo"    column="donor_male_no"    />
        <result property="donorMaleVariety"    column="donor_male_variety"    />
        <result property="embryoVariety"    column="embryo_variety"    />
        <result property="embryoAge"    column="embryo_age"    />
        <result property="gradeAPlus"    column="grade_a_plus"    />
        <result property="gradeA"    column="grade_a"    />
        <result property="gradeB"    column="grade_b"    />
        <result property="gradeC"    column="grade_c"    />
        <result property="gradeD"    column="grade_d"    />
        <result property="cell24"    column="cell_2_4"    />
        <result property="cell8"    column="cell_8"    />
        <result property="cell16"    column="cell_16"    />
        <result property="unfertilized"    column="unfertilized"    />
        <result property="degenerated"    column="degenerated"    />
        <result property="totalEmbryo"    column="total_embryo"    />
        <result property="validEmbryo"    column="valid_embryo"    />
        <result property="transferred"    column="transferred"    />
        <result property="recipientCnt"    column="recipient_cnt"    />
        <result property="embryoType"    column="embryo_type"    />
        <result property="embryoSource"    column="embryo_source"    />
        <result property="destination"    column="destination"    />
        <result property="storageMethod"    column="storage_method"    />
        <result property="flushOperator"    column="flush_operator"    />
        <result property="collectOperator"    column="collect_operator"    />
        <result property="ranchId"    column="ranch_id"    />
        <result property="ranchName"    column="ranch_name"    />
        <result property="remark"    column="remark"    />
    </resultMap>

    <sql id="selectScEmbryoFlushVo">
        select id, flush_time, donor_female_no, donor_female_variety, donor_male_no, donor_male_variety,
               embryo_variety, embryo_age, grade_a_plus, grade_a, grade_b, grade_c, grade_d,
               cell_2_4, cell_8, cell_16, unfertilized, degenerated, total_embryo, valid_embryo,
               transferred, recipient_cnt, embryo_type, embryo_source, destination, storage_method,
               flush_operator, collect_operator, ranch_id, ranch_name, remark
        from sc_embryo_flush
    </sql>

    <select id="selectScEmbryoFlushList" parameterType="ScEmbryoFlush" resultMap="ScEmbryoFlushResult">
        <include refid="selectScEmbryoFlushVo"/>
        <where>
            <if test="params.beginFlushTime != null and params.beginFlushTime != '' and params.endFlushTime != null and params.endFlushTime != ''">
                and flush_time between #{params.beginFlushTime} and #{params.endFlushTime}
            </if>
            <if test="donorFemaleNo != null and donorFemaleNo != ''"> and donor_female_no like concat('%', #{donorFemaleNo}, '%')</if>
            <if test="donorMaleNo != null and donorMaleNo != ''"> and donor_male_no like concat('%', #{donorMaleNo}, '%')</if>
            <if test="embryoVariety != null and embryoVariety != ''"> and embryo_variety = #{embryoVariety}</if>
            <if test="embryoType != null and embryoType != ''"> and embryo_type = #{embryoType}</if>
            <if test="embryoSource != null and embryoSource != ''"> and embryo_source = #{embryoSource}</if>
            <if test="flushOperator != null and flushOperator != ''"> and flush_operator like concat('%', #{flushOperator}, '%')</if>
            <if test="collectOperator != null and collectOperator != ''"> and collect_operator like concat('%', #{collectOperator}, '%')</if>
            <if test="ranchId != null"> and ranch_id = #{ranchId}</if>
        </where>
        order by flush_time desc
    </select>

    <select id="selectScEmbryoFlushById" parameterType="Long" resultMap="ScEmbryoFlushResult">
        <include refid="selectScEmbryoFlushVo"/>
        where id = #{id}
    </select>

    <insert id="insertScEmbryoFlush" parameterType="ScEmbryoFlush" useGeneratedKeys="true" keyProperty="id">
        insert into sc_embryo_flush
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="flushTime != null">flush_time,</if>
            <if test="donorFemaleNo != null and donorFemaleNo != ''">donor_female_no,</if>
            <if test="donorFemaleVariety != null and donorFemaleVariety != ''">donor_female_variety,</if>
            <if test="donorMaleNo != null and donorMaleNo != ''">donor_male_no,</if>
            <if test="donorMaleVariety != null and donorMaleVariety != ''">donor_male_variety,</if>
            <if test="embryoVariety != null and embryoVariety != ''">embryo_variety,</if>
            <if test="embryoAge != null">embryo_age,</if>
            <if test="gradeAPlus != null">grade_a_plus,</if>
            <if test="gradeA != null">grade_a,</if>
            <if test="gradeB != null">grade_b,</if>
            <if test="gradeC != null">grade_c,</if>
            <if test="gradeD != null">grade_d,</if>
            <if test="cell24 != null">cell_2_4,</if>
            <if test="cell8 != null">cell_8,</if>
            <if test="cell16 != null">cell_16,</if>
            <if test="unfertilized != null">unfertilized,</if>
            <if test="degenerated != null">degenerated,</if>
            <if test="totalEmbryo != null">total_embryo,</if>
            <if test="validEmbryo != null">valid_embryo,</if>
            <if test="transferred != null">transferred,</if>
            <if test="recipientCnt != null">recipient_cnt,</if>
            <if test="embryoType != null and embryoType != ''">embryo_type,</if>
            <if test="embryoSource != null and embryoSource != ''">embryo_source,</if>
            <if test="destination != null and destination != ''">destination,</if>
            <if test="storageMethod != null and storageMethod != ''">storage_method,</if>
            <if test="flushOperator != null and flushOperator != ''">flush_operator,</if>
            <if test="collectOperator != null and collectOperator != ''">collect_operator,</if>
            <if test="ranchId != null">ranch_id,</if>
            <if test="ranchName != null and ranchName != ''">ranch_name,</if>
            <if test="remark != null">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="flushTime != null">#{flushTime},</if>
            <if test="donorFemaleNo != null and donorFemaleNo != ''">#{donorFemaleNo},</if>
            <if test="donorFemaleVariety != null and donorFemaleVariety != ''">#{donorFemaleVariety},</if>
            <if test="donorMaleNo != null and donorMaleNo != ''">#{donorMaleNo},</if>
            <if test="donorMaleVariety != null and donorMaleVariety != ''">#{donorMaleVariety},</if>
            <if test="embryoVariety != null and embryoVariety != ''">#{embryoVariety},</if>
            <if test="embryoAge != null">#{embryoAge},</if>
            <if test="gradeAPlus != null">#{gradeAPlus},</if>
            <if test="gradeA != null">#{gradeA},</if>
            <if test="gradeB != null">#{gradeB},</if>
            <if test="gradeC != null">#{gradeC},</if>
            <if test="gradeD != null">#{gradeD},</if>
            <if test="cell24 != null">#{cell24},</if>
            <if test="cell8 != null">#{cell8},</if>
            <if test="cell16 != null">#{cell16},</if>
            <if test="unfertilized != null">#{unfertilized},</if>
            <if test="degenerated != null">#{degenerated},</if>
            <if test="totalEmbryo != null">#{totalEmbryo},</if>
            <if test="validEmbryo != null">#{validEmbryo},</if>
            <if test="transferred != null">#{transferred},</if>
            <if test="recipientCnt != null">#{recipientCnt},</if>
            <if test="embryoType != null and embryoType != ''">#{embryoType},</if>
            <if test="embryoSource != null and embryoSource != ''">#{embryoSource},</if>
            <if test="destination != null and destination != ''">#{destination},</if>
            <if test="storageMethod != null and storageMethod != ''">#{storageMethod},</if>
            <if test="flushOperator != null and flushOperator != ''">#{flushOperator},</if>
            <if test="collectOperator != null and collectOperator != ''">#{collectOperator},</if>
            <if test="ranchId != null">#{ranchId},</if>
            <if test="ranchName != null and ranchName != ''">#{ranchName},</if>
            <if test="remark != null">#{remark},</if>
        </trim>
    </insert>

    <update id="updateScEmbryoFlush" parameterType="ScEmbryoFlush">
        update sc_embryo_flush
        <trim prefix="SET" suffixOverrides=",">
            <if test="flushTime != null">flush_time = #{flushTime},</if>
            <if test="donorFemaleNo != null and donorFemaleNo != ''">donor_female_no = #{donorFemaleNo},</if>
            <if test="donorFemaleVariety != null and donorFemaleVariety != ''">donor_female_variety = #{donorFemaleVariety},</if>
            <if test="donorMaleNo != null and donorMaleNo != ''">donor_male_no = #{donorMaleNo},</if>
            <if test="donorMaleVariety != null and donorMaleVariety != ''">donor_male_variety = #{donorMaleVariety},</if>
            <if test="embryoVariety != null and embryoVariety != ''">embryo_variety = #{embryoVariety},</if>
            <if test="embryoAge != null">embryo_age = #{embryoAge},</if>
            <if test="gradeAPlus != null">grade_a_plus = #{gradeAPlus},</if>
            <if test="gradeA != null">grade_a = #{gradeA},</if>
            <if test="gradeB != null">grade_b = #{gradeB},</if>
            <if test="gradeC != null">grade_c = #{gradeC},</if>
            <if test="gradeD != null">grade_d = #{gradeD},</if>
            <if test="cell24 != null">cell_2_4 = #{cell24},</if>
            <if test="cell8 != null">cell_8 = #{cell8},</if>
            <if test="cell16 != null">cell_16 = #{cell16},</if>
            <if test="unfertilized != null">unfertilized = #{unfertilized},</if>
            <if test="degenerated != null">degenerated = #{degenerated},</if>
            <if test="totalEmbryo != null">total_embryo = #{totalEmbryo},</if>
            <if test="validEmbryo != null">valid_embryo = #{validEmbryo},</if>
            <if test="transferred != null">transferred = #{transferred},</if>
            <if test="recipientCnt != null">recipient_cnt = #{recipientCnt},</if>
            <if test="embryoType != null and embryoType != ''">embryo_type = #{embryoType},</if>
            <if test="embryoSource != null and embryoSource != ''">embryo_source = #{embryoSource},</if>
            <if test="destination != null and destination != ''">destination = #{destination},</if>
            <if test="storageMethod != null and storageMethod != ''">storage_method = #{storageMethod},</if>
            <if test="flushOperator != null and flushOperator != ''">flush_operator = #{flushOperator},</if>
            <if test="collectOperator != null and collectOperator != ''">collect_operator = #{collectOperator},</if>
            <if test="ranchId != null">ranch_id = #{ranchId},</if>
            <if test="ranchName != null and ranchName != ''">ranch_name = #{ranchName},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteScEmbryoFlushById" parameterType="Long">
        delete from sc_embryo_flush where id = #{id}
    </delete>

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

    <!-- 根据耳号查询羊只信息(从sheep_file视图) -->
    <select id="selectSheepInfoByManageTag" parameterType="String" resultType="java.util.Map">
        SELECT
            bs_manage_tags AS manageTag,
            variety_id AS varietyId,
            variety,
            ranch_id AS ranchId,
            dr_ranch AS ranchName
        FROM sheep_file
        WHERE bs_manage_tags = #{manageTag}
            LIMIT 1
    </select>

    <!-- 根据母羊耳号查询最近一条配种记录，获取公羊耳号和配种日期 -->
    <!-- ewe_id存的是数字ID，需要先根据耳号找到母羊ID，再查配种记录，再根据公羊ID找到公羊耳号 -->
    <select id="selectBreedRecordByEwe" resultType="java.util.Map">
        SELECT
            ram_sf.bs_manage_tags AS ramId,
            br.create_time AS matingDate
        FROM sc_breed_record br
                 INNER JOIN sheep_file ewe_sf ON ewe_sf.id = br.ewe_id
                 INNER JOIN sheep_file ram_sf ON ram_sf.id = br.ram_id
        WHERE ewe_sf.bs_manage_tags = #{eweManageTag}
        ORDER BY br.create_time DESC
            LIMIT 1
    </select>

    <!-- 查询所有母羊列表(用于下拉选择) -->
    <select id="selectDonorFemaleList" resultType="java.util.Map">
        SELECT DISTINCT
            sf.bs_manage_tags AS manageTag,
            sf.variety,
            sf.variety_id AS varietyId,
            sf.ranch_id AS ranchId,
            sf.dr_ranch AS ranchName
        FROM sheep_file sf
        WHERE sf.gender = 1
          AND (sf.is_delete = 0 OR sf.is_delete IS NULL)
        ORDER BY sf.bs_manage_tags
    </select>
</mapper>