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

    <select id="selectDistinctSheepIds" resultType="string" parameterType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis">
        SELECT DISTINCT a.sheep_id
        FROM np_milk_prod_classes a
        LEFT JOIN sheep_file sf ON a.sheep_id = sf.id
        <where>
            <if test="manageEarTag != null and manageEarTag != ''">
                AND sf.bs_manage_tags LIKE CONCAT('%', #{manageEarTag}, '%')
            </if>
            <if test="allEarNumbers != null and allEarNumbers.size() > 0">
                AND (
                sf.bs_manage_tags IN
                <foreach collection="allEarNumbers" item="earNumber" open="(" separator="," close=")">
                    #{earNumber}
                </foreach>
                )
            </if>
        </where>
        ORDER BY a.sheep_id
    </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="selectMilkRecordsBySheepId" resultType="map" parameterType="string">
        SELECT
            datetime AS classDate,
            milk AS systemMilk,
            corrected_milk AS correctedMilk,
            classes AS parity
        FROM np_milk_prod_classes
        WHERE sheep_id = #{sheepId}
        ORDER BY datetime ASC
    </select>

    <select id="selectSheepFileBySheepId" resultType="map" parameterType="string">
        SELECT
            id AS sheep_id,
            bs_manage_tags AS manageEarTag,
            variety,
            lactation_day AS lactationDay,
            name AS sheepType,
            birthday,
            parity AS currentParity,
            month_age AS monthAge,
            current_weight AS currentWeight,
            breed AS breedStatus,
            father_manage_tags AS fatherManageTags,
            mother_manage_tags AS motherManageTags,
            dr_ranch AS ranchName,
            family
        FROM sheep_file
        WHERE id = #{sheepId}
    </select>

    <select id="selectNpSheepMilkAnalysisList" resultType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis" parameterType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis">
        SELECT 1 FROM dual WHERE 1=0
    </select>

    <select id="selectNpSheepMilkAnalysisForExport" resultType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis" parameterType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis">
        SELECT
        sf.id as sheepId,
        sf.bs_manage_tags as manageEarTag,
        sf.variety,
        m.datetime as milkingStartTime
        FROM sheep_file sf
        LEFT JOIN np_milk_prod_classes m ON sf.id = m.sheep_id
        <where>
            <if test="manageEarTag != null and manageEarTag != ''">
                AND sf.bs_manage_tags LIKE CONCAT('%', #{manageEarTag}, '%')
            </if>
            <if test="allEarNumbers != null and allEarNumbers.size() > 0">
                AND (
                sf.bs_manage_tags IN
                <foreach collection="allEarNumbers" item="earNumber" open="(" separator="," close=")">
                    #{earNumber}
                </foreach>
                )
            </if>
        </where>
        GROUP BY sf.id
    </select>

</mapper>