<?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.dairyProducts.mapper.NpMilkProdClassesMapper">

    <resultMap id="NpMilkProdClassesResult" type="com.zhyc.module.dairyProducts.domain.NpMilkProdClasses">
        <result property="id" column="id"/>
        <result property="datetime" column="datetime"/>
        <result property="manageEarNo" column="bs_manage_tags"/>
        <result property="electronicEarNo" column="electronic_tags"/>
        <result property="parity" column="parity"/>
        <result property="factory" column="dr_ranch"/>
        <result property="classes" column="classes"/>
        <result property="milk" column="milk"/>
        <result property="correctedMilk" column="corrected_milk"/>
    </resultMap>

    <select id="selectNpMilkProdClassesList" resultMap="NpMilkProdClassesResult">
        SELECT
        mpc.id,
        mpc.datetime,
        v.bs_manage_tags,
        v.electronic_tags,
        v.parity,
        v.dr_ranch,
        mpc.classes,
        mpc.milk,
        mpc.corrected_milk AS corrected_milk
        FROM np_milk_prod_classes mpc
        JOIN sheep_file v ON mpc.sheep_id = v.id
        LEFT JOIN xz_wegih_correction wc ON DATE(mpc.datetime) = DATE(wc.datetime) AND v.dr_ranch = wc.factory
        LEFT JOIN xz_parity_correction pc ON v.parity = pc.parity
        LEFT JOIN xz_dry_matter_correction dmco ON DATE_FORMAT(mpc.datetime, '%Y-%m') = DATE_FORMAT(dmco.datetime, '%Y-%m') AND v.dr_ranch = dmco.standard
        <where>
            <if test="datetimeStart != null">
                AND mpc.datetime &gt;= #{datetimeStart}
            </if>
            <if test="datetimeEnd != null">
                AND mpc.datetime &lt;= #{datetimeEnd}
            </if>

            <if test="npMilkProdClasses.allEarNumbers != null and npMilkProdClasses.allEarNumbers.size() > 0">
                AND (
                v.bs_manage_tags IN
                <foreach collection="npMilkProdClasses.allEarNumbers" item="earNumber" open="(" separator="," close=")">
                    #{earNumber}
                </foreach>
                )
            </if>

            <if test="npMilkProdClasses.factory != null and npMilkProdClasses.factory != ''">
                AND v.dr_ranch = #{npMilkProdClasses.factory}
            </if>
            <if test="npMilkProdClasses.classes != null">
                AND mpc.classes = #{npMilkProdClasses.classes}
            </if>

            ${npMilkProdClasses.params.dataScope}
        </where>
    </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="getWeightCorrection" resultType="java.lang.Double">
        SELECT actual / system_milk
        FROM xz_wegih_correction
        WHERE DATE(datetime) = DATE(#{date})
          AND factory = #{factory}
          AND system_milk > 0
    </select>

    <select id="getParityCorrection" resultType="java.lang.Double">
        SELECT
            CASE
                WHEN parity = 1 THEN 1.2
                WHEN parity BETWEEN 2 AND 4 THEN 0.96
                ELSE 1
                END
        FROM xz_parity_correction
        WHERE parity = #{parity}
    </select>

    <select id="getDryMatterCorrection" resultType="java.lang.Double">
        SELECT coefficient
        FROM xz_dry_matter_correction
        WHERE DATE_FORMAT(datetime, '%Y-%m') = DATE_FORMAT(#{date}, '%Y-%m')
          AND factory = #{factory}
    </select>

    <insert id="insertNpMilkProdClasses" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO np_milk_prod_classes (datetime, sheep_id, classes, milk, corrected_milk, user_id, dept_id)
        VALUES (#{datetime}, #{sheepId}, #{classes}, #{milk}, #{correctedMilk}, #{userId}, #{deptId})
    </insert>

    <select id="selectSheepIdByManageEarNo" resultType="java.lang.String">
        SELECT id
        FROM sheep_file
        WHERE bs_manage_tags = #{manageEarNo}
    </select>
</mapper>