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

    <resultMap type="ScDryMilk" id="ScDryMilkResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="datetime"    column="datetime"    />
        <result property="status"    column="status"    />
        <result property="sheepfold"    column="sheepfold"    />
        <result property="tecahnician"    column="tecahnician"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="comment"    column="comment"    />
        <!-- 联表查询字段 -->
        <result property="manageTags"    column="bs_manage_tags"    />
        <result property="variety"    column="variety"    />
        <result property="sheepfoldName"    column="sheepfold_name"    />
        <result property="eventType"    column="event_type"    />
    </resultMap>

    <sql id="selectScDryMilkVo">
        select d.id, d.sheep_id, d.datetime, d.status, d.sheepfold, d.tecahnician,
               d.create_by, d.create_time, d.comment,
               s.bs_manage_tags, s.variety, s.sheepfold_name,
               '干奶' as event_type
        from sc_dry_milk d
                 left join sheep_file s on d.sheep_id = s.id
    </sql>

    <select id="selectScDryMilkList" parameterType="ScDryMilk" resultMap="ScDryMilkResult">
        <include refid="selectScDryMilkVo"/>
        <where>
            <if test="manageTagsList != null and manageTagsList.size() > 0">
                AND s.bs_manage_tags IN
                <foreach collection="manageTagsList" item="tag" open="(" separator="," close=")">
                    #{tag}
                </foreach>
            </if>
            <if test="(manageTagsList == null or manageTagsList.size() == 0) and manageTags != null and manageTags != ''">
                AND s.bs_manage_tags like concat('%', #{manageTags}, '%')
            </if>

            <if test="technicianList != null and technicianList.size() > 0">
                AND d.tecahnician IN
                <foreach collection="technicianList" item="tech" open="(" separator="," close=")">
                    #{tech}
                </foreach>
            </if>
            <if test="(technicianList == null or technicianList.size() == 0) and tecahnician != null and tecahnician != ''">
                AND d.tecahnician like concat('%', #{tecahnician}, '%')
            </if>

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

            <if test="status != null "> and d.status = #{status}</if>
            <if test="sheepfold != null "> and d.sheepfold = #{sheepfold}</if>
            <if test="variety != null  and variety != ''"> and s.variety like concat('%', #{variety}, '%')</if>
        </where>
        order by d.datetime desc
    </select>

    <select id="selectSheepEarNumberList" resultType="String">
        SELECT bs_manage_tags
        FROM sheep_file
        WHERE bs_manage_tags LIKE concat('%', #{query}, '%')
            LIMIT 20
    </select>

    <select id="searchTechnicianList" resultType="String">
        SELECT nick_name
        FROM sys_user
        WHERE nick_name LIKE concat('%', #{query}, '%')
            LIMIT 20
    </select>

    <select id="selectScDryMilkById" parameterType="Long" resultMap="ScDryMilkResult">
        <include refid="selectScDryMilkVo"/>
        where d.id = #{id}
    </select>


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

    <insert id="insertScDryMilk" parameterType="ScDryMilk" useGeneratedKeys="true" keyProperty="id">
        insert into sc_dry_milk
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="datetime != null">datetime,</if>
            <if test="status != null">status,</if>
            <if test="sheepfold != null">sheepfold,</if>
            <if test="tecahnician != null">tecahnician,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="comment != null">comment,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="datetime != null">#{datetime},</if>
            <if test="status != null">#{status},</if>
            <if test="sheepfold != null">#{sheepfold},</if>
            <if test="tecahnician != null">#{tecahnician},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="comment != null">#{comment},</if>
        </trim>
    </insert>

    <update id="updateScDryMilk" parameterType="ScDryMilk">
        update sc_dry_milk
        <trim prefix="SET" suffixOverrides=",">
            <if test="sheepId != null">sheep_id = #{sheepId},</if>
            <if test="datetime != null">datetime = #{datetime},</if>
            <if test="status != null">status = #{status},</if>
            <if test="sheepfold != null">sheepfold = #{sheepfold},</if>
            <if test="tecahnician != null">tecahnician = #{tecahnician},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="comment != null">comment = #{comment},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteScDryMilkById" parameterType="Long">
        delete from sc_dry_milk where id = #{id}
    </delete>

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

    <!-- 当 gender=null 时，不会添加 gender 条件，即查询全部羊 -->
    <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>
</mapper>