<?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.ruoyi.app.mapper.PorkOtherProduceMapper">
    
    <resultMap type="PorkOtherProduce" id="PorkOtherProduceResult">
        <result property="id"    column="id"    />
        <result property="productName"    column="product_name"    />
        <result property="produceTime"    column="produce_time"    />
        <result property="slaughterCode"    column="slaughter_code"    />
        <result property="distributeBatchId"    column="distribute_batch_id"    />
        <result property="entranceBatchId"    column="entrance_batch_id"    />
        <result property="scaleWeight"    column="scale_weight"    />
        <result property="finalWeight"    column="final_weight"    />
        <result property="remark"    column="remark"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="delFlag"    column="del_flag"    />
    </resultMap>

    <resultMap type="com.ruoyi.app.DTO.PorkOtherProduceDTO" id="PorkOtherProduceDTOResult">
        <result property="id"    column="id"    />
        <result property="productName"    column="product_name"    />
        <result property="produceTime"    column="produce_time"    />
        <result property="slaughterCode"    column="slaughter_code"    />
        <result property="distributeBatchId"    column="distribute_batch_id"    />
        <result property="entranceBatchId"    column="entrance_batch_id"    />
        <result property="scaleWeight"    column="scale_weight"    />
        <result property="finalWeight"    column="final_weight"    />
        <result property="remark"    column="remark"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="purchaserName"    column="purchaser_name"    />
        <result property="supplierName"    column="supplier_name"    />
        <result property="animalCertNo"    column="animal_cert_no"    />
    </resultMap>

    <resultMap type="com.ruoyi.app.DTO.TimeAndWeightDTO" id="TimeAndWeightDTOResult">
        <result property="timeSpot"    column="time_spot"    />
        <result property="totalWeight"    column="total_weight"    />
    </resultMap>

    <sql id="selectPorkOtherProduceVo">
        select id, product_name, produce_time, slaughter_code, distribute_batch_id, entrance_batch_id, scale_weight, final_weight, remark, create_by, update_time, update_by, del_flag from pork_other_produce
    </sql>

    <select id="selectPorkOtherProduceList" parameterType="com.ruoyi.app.domain.request.ReqGetPorkOtherProduce" resultMap="PorkOtherProduceDTOResult">
        select a.*,c.purchaser_name,d.supplier_name,e.animal_cert_no
        from pork_other_produce as a
        left join distribute_batch as b on b.id = a.distribute_batch_id and b.del_flag = '0'
        left join purchaser as c on c.id = b.purchaser_id and c.del_flag = '0'
        left join supplier as d on d.id = b.supplier_id and d.del_flag = '0'
        left join entrance_batch as e on e.id = a.entrance_batch_id and e.del_flag = '0'
        <where>
            a.del_flag = '0'
            <if test="productName != null and animalCertNo != ''"> and a.product_name = #{productName}</if>
            <if test="keyword != null  and keyword != ''"> and (e.animal_cert_no like concat('%', #{keyword}, '%') or a.slaughter_code like concat('%', #{keyword}, '%'))</if>
            <if test="animalCertNo != null  and animalCertNo != ''"> and e.animal_cert_no like concat('%', #{animalCertNo}, '%')</if>
            <if test="produceTime != null "> and DATE(a.produce_time) = DATE(#{produceTime})</if>
            <if test="slaughterCode != null  and slaughterCode != ''"> and a.slaughter_code like concat('%', #{slaughterCode}, '%')</if>
        </where>
        order by a.produce_time desc
    </select>
    
    <select id="selectPorkOtherProduceById" parameterType="Long" resultMap="PorkOtherProduceDTOResult">
        select a.*,c.purchaser_name,d.supplier_name,e.animal_cert_no
        from pork_other_produce as a
                 left join distribute_batch as b on b.id = a.distribute_batch_id and b.del_flag = '0'
                 left join purchaser as c on c.id = b.purchaser_id and c.del_flag = '0'
                 left join supplier as d on d.id = b.supplier_id and d.del_flag = '0'
                 left join entrance_batch as e on e.id = a.entrance_batch_id and e.del_flag = '0'
        where a.id = #{id} and a.del_flag = '0'
    </select>

    <insert id="insertPorkOtherProduce" parameterType="PorkOtherProduce" useGeneratedKeys="true" keyProperty="id">
        insert into pork_other_produce
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="productName != null and productName != ''">product_name,</if>
            <if test="produceTime != null">produce_time,</if>
            <if test="slaughterCode != null">slaughter_code,</if>
            <if test="distributeBatchId != null">distribute_batch_id,</if>
            <if test="entranceBatchId != null">entrance_batch_id,</if>
            <if test="scaleWeight != null">scale_weight,</if>
            <if test="finalWeight != null">final_weight,</if>
            <if test="remark != null">remark,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            del_flag
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="productName != null and productName != ''">#{productName},</if>
            <if test="produceTime != null">#{produceTime},</if>
            <if test="slaughterCode != null">#{slaughterCode},</if>
            <if test="distributeBatchId != null">#{distributeBatchId},</if>
            <if test="entranceBatchId != null">#{entranceBatchId},</if>
            <if test="scaleWeight != null">#{scaleWeight},</if>
            <if test="finalWeight != null">#{finalWeight},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            '0'
         </trim>
    </insert>

    <update id="updatePorkOtherProduce" parameterType="PorkOtherProduce">
        update pork_other_produce
        <trim prefix="SET" suffixOverrides=",">
            <if test="productName != null and productName != ''">product_name = #{productName},</if>
            <if test="produceTime != null">produce_time = #{produceTime},</if>
            <if test="slaughterCode != null">slaughter_code = #{slaughterCode},</if>
            <if test="distributeBatchId != null">distribute_batch_id = #{distributeBatchId},</if>
            <if test="entranceBatchId != null">entrance_batch_id = #{entranceBatchId},</if>
            <if test="scaleWeight != null">scale_weight = #{scaleWeight},</if>
            <if test="finalWeight != null">final_weight = #{finalWeight},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deletePorkOtherProduceById" parameterType="Long">
        update pork_other_produce set del_flag = '2' where id = #{id}
    </delete>

    <delete id="deletePorkOtherProduceByIds" parameterType="String">
        update pork_other_produce set del_flag = '2' where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!--根据时间范围平均重量-->
    <select id="selectAvgWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultType="BigDecimal">
        select IFNULL(avg(final_weight),0) from pork_other_produce
        <where>
            del_flag = '0'
            <if test="keyword != null "> and product_name = #{keyword}</if>
            <if test="startTime != null "> and produce_time <![CDATA[ >= ]]> #{startTime}</if>
            <if test="endTime != null "> and produce_time <![CDATA[ <= ]]> #{endTime}</if>
        </where>
    </select>

    <!--统计按年全部数据指定产品累计重量-->
    <select id="selectAllYearWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultMap="TimeAndWeightDTOResult">
        SELECT
            YEAR(produce_time) AS time_spot,
            IFNULL(SUM(final_weight),0) AS total_weight
        FROM
            pork_other_produce
        <where>
            del_flag = '0'
            <if test="keyword != null "> and product_name = #{keyword}</if>
        </where>

        GROUP BY
            YEAR(produce_time)
        ORDER BY
            time_spot;
    </select>

    <!--根据获取近一年每月指定产品累计重量-->
    <select id="selectYearWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultMap="TimeAndWeightDTOResult">
        SELECT
            months.month_period as time_spot,
            IFNULL(SUM(eb.final_weight), 0) AS total_weight
        FROM (
                 SELECT
                     DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL seq MONTH), '%Y-%m') AS month_period,
                     DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL seq MONTH) AS month_start,
                     LAST_DAY(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL seq MONTH)) AS month_end
                 FROM (
                          SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
                          SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
                          SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
                          SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
                      ) AS sequences
             ) AS months
                 LEFT JOIN pork_other_produce eb ON
            eb.produce_time BETWEEN months.month_start AND months.month_end
                and eb.del_flag = '0'
            <if test="keyword != null "> and eb.product_name = #{keyword}</if>
        GROUP BY
            months.month_period, months.month_start, months.month_end
        ORDER BY
            months.month_start;
    </select>

    <!--根据获取近三十天的每日指定产品累计重量-->
    <select id="selectMonthWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultMap="TimeAndWeightDTOResult">
        SELECT
            dates.date AS time_spot,
            IFNULL(SUM(eb.final_weight), 0) AS total_weight
        FROM (
                 SELECT DATE_SUB(CURDATE(), INTERVAL seq DAY) AS date
                 FROM (
                     SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
                     SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
                     SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
                     SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
                     SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
                     SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
                     SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
                     SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
                     SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
                     SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
                     ) AS sequences
             ) AS dates
                 LEFT JOIN pork_other_produce eb ON
            DATE(eb.produce_time) = dates.date and eb.del_flag = '0'
        <if test="keyword != null "> and eb.product_name = #{keyword}</if>
        GROUP BY
            dates.date
        ORDER BY
            dates.date;
    </select>

    <!--根据获取近七天的每日指定产品累计重量-->
    <select id="selectWeekWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultMap="TimeAndWeightDTOResult">
        SELECT
            dates.date AS time_spot,
            IFNULL(SUM(eb.final_weight), 0) AS total_weight
        FROM (
                 SELECT CURDATE() AS date UNION ALL
                 SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) UNION ALL
                 SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) UNION ALL
                 SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) UNION ALL
                 SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) UNION ALL
                 SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) UNION ALL
                 SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY)
             ) AS dates
                 LEFT JOIN pork_other_produce eb ON
            DATE(eb.produce_time) = dates.date and eb.del_flag = '0'
        <if test="keyword != null "> and eb.product_name = #{keyword}</if>
        GROUP BY
            dates.date
        ORDER BY
            dates.date;
    </select>

    <!--根据获取每小时指定产品累计重量-->
    <select id="selectTodayWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultMap="TimeAndWeightDTOResult">
        SELECT
            hours.hour AS time_spot,
            IFNULL(SUM(eb.final_weight), 0) AS total_weight
        FROM (
                 SELECT '00:00' AS hour UNION SELECT '01:00' UNION SELECT '02:00' UNION SELECT '03:00'
                 UNION SELECT '04:00' UNION SELECT '05:00' UNION SELECT '06:00' UNION SELECT '07:00'
                 UNION SELECT '08:00' UNION SELECT '09:00' UNION SELECT '10:00' UNION SELECT '11:00'
                 UNION SELECT '12:00' UNION SELECT '13:00' UNION SELECT '14:00' UNION SELECT '15:00'
                 UNION SELECT '16:00' UNION SELECT '17:00' UNION SELECT '18:00' UNION SELECT '19:00'
                 UNION SELECT '20:00' UNION SELECT '21:00' UNION SELECT '22:00' UNION SELECT '23:00'
             ) AS hours
                 LEFT JOIN pork_other_produce eb ON
            DATE_FORMAT(eb.produce_time, '%H:00') = hours.hour
                AND DATE(eb.produce_time) = CURDATE() and eb.del_flag = '0'
              <if test="keyword != null "> and eb.product_name = #{keyword}</if>
        GROUP BY
            hours.hour
        ORDER BY
            hours.hour;
    </select>

    <!--根据获取指定时间范围内每日指定产品累计重量-->
    <select id="selectRangeWeightByTime" parameterType="com.ruoyi.app.domain.request.SearchTimeRangeParam" resultMap="TimeAndWeightDTOResult">
        SELECT
            DATE_FORMAT(dates.date, '%Y-%m-%d') AS time_spot,
            IFNULL(SUM(eb.final_weight), 0) AS total_weight
        FROM (
                 SELECT DATE_ADD(#{startTime}, INTERVAL seq DAY) AS date
                 FROM (
                     SELECT a.n + b.n*10 + c.n*100 AS seq
                     FROM
                     (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                     (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
                     (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
                     WHERE a.n + b.n*10 + c.n*100 <![CDATA[ <= ]]> DATEDIFF(#{endTime}, #{startTime})
                     ) AS sequences
             ) AS dates
                 LEFT JOIN pork_other_produce eb ON
            DATE(eb.produce_time) = dates.date
            AND eb.del_flag = '0'
        <if test="keyword != null "> and eb.product_name = #{keyword}</if>
        GROUP BY
            dates.date
        ORDER BY
            dates.date
    </select>

    <!--根据分销ID统计生产记录数量-->
    <select id="countRecordByDistributeId" parameterType="Long" resultType="Integer">
        select count(1) from pork_other_produce
        where distribute_batch_id = #{distributeBatchId} and del_flag = '0'
    </select>
</mapper>