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

    <resultMap type="ScWeanRecord" id="ScWeanRecordResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="datetime"    column="datetime"    />
        <result property="weight"    column="weight"    />
        <result property="status"    column="status"    />
        <result property="technician"    column="technician"    />
        <result property="comment"    column="comment"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="electronicTags"    column="electronic_tags"    />

        <result property="earNumber"    column="bs_manage_tags"    />
        <result property="breed"    column="variety"    />
        <result property="eventType"    column="event_type"    />
        <result property="gender"    column="gender"    />
        <result property="fatherNumber"    column="father_manage_tags"    />
        <result property="motherNumber"    column="mother_manage_tags"    />
        <result property="monthAge"    column="month_age"    />
        <result property="birthWeight"    column="birth_weight"    />
        <result property="sheepPen"    column="sheepfold_name"    />
        <result property="breedingStatus"    column="breed"    />
    </resultMap>

    <sql id="selectScWeanRecordVo">
        select
            wr.id, wr.sheep_id, wr.datetime, wr.weight, wr.status, wr.technician,
            wr.comment, wr.create_by, wr.create_time, wr.electronic_tags,
            sf.bs_manage_tags, sf.variety, sf.gender, sf.father_manage_tags, sf.mother_manage_tags,
            sf.birth_weight, sf.sheepfold_name, sf.breed, sf.month_age,
            '断奶' as event_type
        from sc_wean_record wr
                 left join sheep_file sf on wr.sheep_id = sf.id
    </sql>

    <select id="selectScWeanRecordList" parameterType="ScWeanRecord" resultMap="ScWeanRecordResult">
        <include refid="selectScWeanRecordVo"/>
        <where>
            <!-- 全部羊多耳号查询 -->
            <!-- 正确：sf.bs_manage_tags，sf是sheep_file的别名 -->
            <!-- 全部羊多耳号查询 -->
            <if test="allEarNumbers != null and allEarNumbers.size() > 0">
                AND (
                <!-- 注意：s 代表 sheep_file 表的别名，根据实际 SQL 别名修改 -->
                sf.bs_manage_tags IN
                <foreach collection="allEarNumbers" item="earNumber" open="(" separator="," close=")">
                    #{earNumber}
                </foreach>
                )
            </if>
            <if test="sheepId != null "> and wr.sheep_id = #{sheepId}</if>

            <if test="allEarNumbers != null and allEarNumbers.size() > 0">
                AND sf.bs_manage_tags IN
                <foreach collection="allEarNumbers" item="ear" open="(" separator="," close=")">
                    #{ear}
                </foreach>
            </if>
            <if test="(allEarNumbers == null or allEarNumbers.size() == 0) and earNumber != null and earNumber != ''">
                and sf.bs_manage_tags like concat('%', #{earNumber}, '%')
            </if>

            <if test="params.beginTime != null and params.beginTime != ''">
                AND date_format(wr.datetime,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params.endTime != null and params.endTime != ''">
                AND date_format(wr.datetime,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>

            <if test="isInHerd != null and isInHerd != ''">
                AND sf.status = #{isInHerd}
            </if>

            <if test="sheepCategory != null and sheepCategory != ''">
                AND sf.breed = #{sheepCategory}
            </if>

            <if test="weight != null "> and wr.weight = #{weight}</if>
            <if test="status != null "> and wr.status = #{status}</if>
            <if test="technician != null  and technician != ''"> and wr.technician like concat('%', #{technician}, '%')</if>
            <if test="comment != null  and comment != ''"> and wr.comment like concat('%', #{comment}, '%')</if>
            <if test="createBy != null  and createBy != ''"> and wr.create_by like concat('%', #{createBy}, '%')</if>
            <if test="createTime != null "> and wr.create_time = #{createTime}</if>
            <if test="electronicTags != null  and electronicTags != ''"> and wr.electronic_tags like concat('%', #{electronicTags}, '%')</if>
            <if test="breed != null  and breed != ''"> and sf.variety like concat('%', #{breed}, '%')</if>
            <if test="gender != null  and gender != ''"> and sf.gender = #{gender}</if>
            <if test="fatherNumber != null  and fatherNumber != ''"> and sf.father_manage_tags like concat('%', #{fatherNumber}, '%')</if>
            <if test="motherNumber != null  and motherNumber != ''"> and sf.mother_manage_tags like concat('%', #{motherNumber}, '%')</if>
            <if test="sheepPen != null  and sheepPen != ''"> and sf.sheepfold_name like concat('%', #{sheepPen}, '%')</if>
        </where>
        order by wr.create_time desc
    </select>

    <!-- 模糊查询耳号列表 -->
    <select id="searchEarNumbers" resultType="java.lang.String">
        SELECT DISTINCT sf.bs_manage_tags
        FROM sheep_file sf
        WHERE sf.bs_manage_tags LIKE CONCAT(#{query}, '%')
          AND sf.is_delete = 0
        ORDER BY sf.bs_manage_tags
            LIMIT 50
    </select>

    <select id="selectScWeanRecordById" parameterType="Long" resultMap="ScWeanRecordResult">
        <include refid="selectScWeanRecordVo"/>
        where wr.id = #{id}
    </select>

    <select id="selectSheepIdByEarNumber" parameterType="String" resultType="Long">
        select id from sheep_file where bs_manage_tags = #{earNumber}
    </select>

    <insert id="insertScWeanRecord" parameterType="ScWeanRecord" useGeneratedKeys="true" keyProperty="id">
        insert into sc_wean_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="datetime != null">datetime,</if>
            <if test="weight != null">weight,</if>
            <if test="status != null">status,</if>
            <if test="technician != null">technician,</if>
            <if test="comment != null">comment,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="electronicTags != null">electronic_tags,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="datetime != null">#{datetime},</if>
            <if test="weight != null">#{weight},</if>
            <if test="status != null">#{status},</if>
            <if test="technician != null">#{technician},</if>
            <if test="comment != null">#{comment},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="electronicTags != null">#{electronicTags},</if>
        </trim>
    </insert>

    <update id="updateScWeanRecord" parameterType="ScWeanRecord">
        update sc_wean_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="sheepId != null">sheep_id = #{sheepId},</if>
            <if test="datetime != null">datetime = #{datetime},</if>
            <if test="weight != null">weight = #{weight},</if>
            <if test="status != null">status = #{status},</if>
            <if test="technician != null">technician = #{technician},</if>
            <if test="comment != null">comment = #{comment},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="electronicTags != null">electronic_tags = #{electronicTags},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteScWeanRecordById" parameterType="Long">
        delete from sc_wean_record where id = #{id}
    </delete>

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

</mapper>