<?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.bodyManage.mapper.ScBodyMeasureMapper">

    <resultMap type="com.zhyc.module.produce.bodyManage.domain.ScBodyMeasure" id="ScBodyMeasureResult">
        <result property="id" column="id"/>
        <result property="sheepId" column="sheep_id"/>
        <result property="manageTags" column="manage_tags"/>
        <result property="sheepfoldId" column="sheepfold_id"/>
        <result property="sheepfoldName" column="sheepfold_name"/>
        <result property="varietyId" column="variety_id"/>
        <result property="varietyName" column="variety_name"/>
        <result property="eventType" column="event_type"/>
        <result property="sheepTypeName" column="sheep_type_name"/>
        <result property="measureDate" column="measure_date"/>
        <result property="gender" column="gender"/>
        <result property="parity" column="parity"/>
        <result property="birthWeight" column="birth_weight"/>
        <result property="weaningWeight" column="weaning_weight"/>
        <result property="currentWeight" column="current_weight"/>
        <result property="height" column="height"/>
        <result property="bust" column="bust"/>
        <result property="bodyLength" column="body_length"/>
        <result property="pipeLength" column="pipe_length"/>
        <result property="chestDepth" column="chest_depth"/>
        <result property="hipHeight" column="hip_height"/>
        <result property="rumpWidth" column="rump_width"/>
        <result property="rumpHeignt" column="rump_heignt"/>
        <result property="hipWidth" column="hip_width"/>
        <result property="hipCrossHeight" column="hip_cross_height"/>
        <result property="monthAge" column="month_age"/>
        <result property="breastDepth" column="breast_depth"/>
        <result property="breastPosition" column="breast_position"/>
        <result property="breastLength" column="breast_length"/>
        <result property="breastAdbere" column="breast_adbere"/>
        <result property="breastSpacing" column="breast_spacing"/>
        <result property="breastScore" column="breast_score"/>
        <result property="bodyScore" column="body_score"/>
        <result property="lactationDay" column="lactation_day"/>
        <result property="gestationDay" column="gestation_day"/>
        <result property="postMatingDay" column="post_mating_day"/>
        <result property="breedStatusName" column="breed_status_name"/>
        <result property="comment" column="comment"/>
        <result property="technician" column="technician"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <sql id="selectScBodyMeasureVo">
        select sm.id,
               sm.sheep_id,
               sm.user_id,
               sm.dept_id,
               bs.manage_tags,
               ds.id             as sheepfold_id,
               ds.sheepfold_name,
               bsv.id            as variety_id,
               bsv.variety       as variety_name,
               '体尺测量'        as event_type,
               bst.name          as sheep_type_name,
               sm.measure_date,
               bs.gender,
               bs.parity,
               bs.birth_weight   as birth_weight,
               bs.weaning_weight as weaning_weight,
               sm.current_weight as current_weight,
               sm.height,
               sm.bust,
               sm.body_length,
               sm.pipe_length,
               sm.chest_depth,
               sm.hip_height,
               sm.rump_width,
               sm.rump_heignt,
               sm.hip_width,
               sm.hip_cross_height,
               sm.month_age,
               sm.breast_depth,
               sm.breast_position,
               sm.breast_length,
               sm.breast_adbere,
               sm.breast_spacing,
               sm.breast_score,
               sm.body_score,
               sm.lactation_day,
               sm.gestation_day,
               sm.post_mating_day,
               bbs.breed         as breed_status_name,
               sm.comment,
               sm.technician,
               sm.create_by,
               sm.create_time
        from sc_body_measure sm
                 LEFT JOIN bas_sheep bs ON sm.sheep_id = bs.id
                 LEFT JOIN bas_breed_status bbs ON bs.breed_status_id = bbs.id
                 LEFT JOIN da_sheepfold ds ON bs.sheepfold_id = ds.id
                 LEFT JOIN bas_sheep_type bst ON bs.type_id = bst.id
                 LEFT JOIN bas_sheep_variety bsv ON bs.variety_id = bsv.id
    </sql>

    <select id="selectScBodyMeasureList" resultMap="ScBodyMeasureResult">
        <include refid="selectScBodyMeasureVo"/>
        <where>
            <if test="sc.ids != null and sc.ids.length > 0">
                AND sm.id IN
                <foreach collection="sc.ids" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="sc.ids == null or sc.ids.length == 0">
                <if test="manageTagsList != null and manageTagsList.size() > 0">
                    <foreach collection="manageTagsList" item="tag" separator="or" open="and (" close=")">
                        bs.manage_tags like concat('%', #{tag}, '%')
                    </foreach>
                </if>
                <if test="sc.sheepId != null">and sm.sheep_id = #{sc.sheepId}</if>
                <if test="sc.sheepfoldId != null">and bs.sheepfold_id = #{sc.sheepfoldId}</if>
                <if test="sc.varietyId != null">and bsv.id = #{sc.varietyId}</if>
                <if test="sc.sheepTypeId != null">and bs.type_id = #{sc.sheepTypeId}</if>
                <if test="sc.params != null and sc.params.beginMeasureDate != null and sc.params.beginMeasureDate != '' and sc.params.endMeasureDate != null and sc.params.endMeasureDate != ''">
                    and sm.measure_date between #{sc.params.beginMeasureDate} and #{sc.params.endMeasureDate}
                </if>
                <if test="sc.params != null and sc.params.beginCreateTime != null and sc.params.beginCreateTime != '' and sc.params.endCreateTime != null and sc.params.endCreateTime != ''">
                    and sm.create_time between #{sc.params.beginCreateTime} and #{sc.params.endCreateTime}
                </if>
                <if test="sc.gender != null and sc.gender != ''">
                    and bs.gender = #{sc.gender}
                </if>
                <if test="sc.technician != null and sc.technician != ''">
                    and sm.technician like concat('%', #{sc.technician}, '%')
                </if>
                <if test="sc.isDelete != null">
                    and bs.is_delete = #{sc.isDelete}
                </if>
                <if test="sc.monthAgeStart != null">
                    and <![CDATA[ sm.month_age >= #{sc.monthAgeStart} ]]>
                </if>
                <if test="sc.monthAgeEnd != null">
                    and <![CDATA[ sm.month_age <= #{sc.monthAgeEnd} ]]>
                </if>
                <if test="sc.breedStatusName != null and sc.breedStatusName != ''">
                    and bbs.breed = #{sc.breedStatusName}
                </if>
                <if test="sc.deptId != null">
                    and sm.dept_id = #{sc.deptId}
                </if>
                <if test="sc.userId != null">
                    and sm.user_id = #{sc.userId}
                </if>
            </if>
            ${sc.params.dataScope}
        </where>
        ORDER BY sm.create_time DESC
    </select>

    <select id="selectScBodyMeasureById" parameterType="Long" resultMap="ScBodyMeasureResult">
        <include refid="selectScBodyMeasureVo"/>
        where sm.id = #{id}
    </select>

    <insert id="insertScBodyMeasure" parameterType="ScBodyMeasure" useGeneratedKeys="true" keyProperty="id">
        insert into sc_body_measure
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="measureDate != null">measure_date,</if>
            <if test="height != null">height,</if>
            <if test="bust != null">bust,</if>
            <if test="bodyLength != null">body_length,</if>
            <if test="pipeLength != null">pipe_length,</if>
            <if test="chestDepth != null">chest_depth,</if>
            <if test="hipHeight != null">hip_height,</if>
            <if test="rumpWidth != null">rump_width,</if>
            <if test="rumpHeignt != null">rump_heignt,</if>
            <if test="currentWeight != null">current_weight,</if>
            <if test="hipWidth != null">hip_width,</if>
            <if test="hipCrossHeight != null">hip_cross_height,</if>
            <if test="monthAge != null">month_age,</if>
            <if test="breastDepth != null">breast_depth,</if>
            <if test="breastPosition != null">breast_position,</if>
            <if test="breastLength != null">breast_length,</if>
            <if test="breastAdbere != null">breast_adbere,</if>
            <if test="breastSpacing != null">breast_spacing,</if>
            <if test="breastScore != null">breast_score,</if>
            <if test="bodyScore != null">body_score,</if>
            <if test="lactationDay != null">lactation_day,</if>
            <if test="gestationDay != null">gestation_day,</if>
            <if test="postMatingDay != null">post_mating_day,</if>
            <if test="comment != null">comment,</if>
            <if test="technician != null">technician,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="userId != null">user_id,</if>
            <if test="deptId != null">dept_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="measureDate != null">#{measureDate},</if>
            <if test="height != null">#{height},</if>
            <if test="bust != null">#{bust},</if>
            <if test="bodyLength != null">#{bodyLength},</if>
            <if test="pipeLength != null">#{pipeLength},</if>
            <if test="chestDepth != null">#{chestDepth},</if>
            <if test="hipHeight != null">#{hipHeight},</if>
            <if test="rumpWidth != null">#{rumpWidth},</if>
            <if test="rumpHeignt != null">#{rumpHeignt},</if>
            <if test="currentWeight != null">#{currentWeight},</if>
            <if test="hipWidth != null">#{hipWidth},</if>
            <if test="hipCrossHeight != null">#{hipCrossHeight},</if>
            <if test="monthAge != null">#{monthAge},</if>
            <if test="breastDepth != null">#{breastDepth},</if>
            <if test="breastPosition != null">#{breastPosition},</if>
            <if test="breastLength != null">#{breastLength},</if>
            <if test="breastAdbere != null">#{breastAdbere},</if>
            <if test="breastSpacing != null">#{breastSpacing},</if>
            <if test="breastScore != null">#{breastScore},</if>
            <if test="bodyScore != null">#{bodyScore},</if>
            <if test="lactationDay != null">#{lactationDay},</if>
            <if test="gestationDay != null">#{gestationDay},</if>
            <if test="postMatingDay != null">#{postMatingDay},</if>
            <if test="comment != null">#{comment},</if>
            <if test="technician != null">#{technician},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="userId != null">#{userId},</if>
            <if test="deptId != null">#{deptId},</if>
        </trim>
    </insert>

    <update id="updateScBodyMeasure" parameterType="ScBodyMeasure">
        update sc_body_measure
        <trim prefix="SET" suffixOverrides=",">
            <if test="sheepId != null">sheep_id = #{sheepId},</if>
            <if test="measureDate != null">measure_date = #{measureDate},</if>
            <if test="height != null">height = #{height},</if>
            <if test="bust != null">bust = #{bust},</if>
            <if test="bodyLength != null">body_length = #{bodyLength},</if>
            <if test="pipeLength != null">pipe_length = #{pipeLength},</if>
            <if test="chestDepth != null">chest_depth = #{chestDepth},</if>
            <if test="hipHeight != null">hip_height = #{hipHeight},</if>
            <if test="rumpWidth != null">rump_width = #{rumpWidth},</if>
            <if test="rumpHeignt != null">rump_heignt = #{rumpHeignt},</if>
            <if test="currentWeight != null">current_weight = #{currentWeight},</if>
            <if test="hipWidth != null">hip_width = #{hipWidth},</if>
            <if test="hipCrossHeight != null">hip_cross_height = #{hipCrossHeight},</if>
            <if test="monthAge != null">month_age = #{monthAge},</if>
            <if test="breastDepth != null">breast_depth = #{breastDepth},</if>
            <if test="breastPosition != null">breast_position = #{breastPosition},</if>
            <if test="breastLength != null">breast_length = #{breastLength},</if>
            <if test="breastAdbere != null">breast_adbere = #{breastAdbere},</if>
            <if test="breastSpacing != null">breast_spacing = #{breastSpacing},</if>
            <if test="breastScore != null">breast_score = #{breastScore},</if>
            <if test="bodyScore != null">body_score = #{bodyScore},</if>
            <if test="lactationDay != null">lactation_day = #{lactationDay},</if>
            <if test="gestationDay != null">gestation_day = #{gestationDay},</if>
            <if test="postMatingDay != null">post_mating_day = #{postMatingDay},</if>
            <if test="comment != null">comment = #{comment},</if>
            <if test="technician != null">technician = #{technician},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteScBodyMeasureById" parameterType="Long">
        delete
        from sc_body_measure
        where id = #{id}
    </delete>

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

    <select id="searchEarNumbers" resultType="java.lang.String">
        SELECT DISTINCT bs.manage_tags
        FROM bas_sheep bs
        WHERE bs.manage_tags LIKE CONCAT('%', #{query}, '%')
          AND bs.is_delete = 0
        ORDER BY bs.manage_tags
    </select>

    <select id="selectBreedStatusList" resultType="java.util.HashMap">
        SELECT id, breed as label, breed as value
        FROM bas_breed_status
        ORDER BY id
    </select>
</mapper>