<?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.NpMilkInOutStoreMapper">
    <select id="selectWithColumns" resultType="map">
        SELECT s.datetime, s.num, s.colost_sheep, s.commercial_intake, s.anti_intake,
        s.colost_intake, s.intake_total, s.commercial_test, s.colost_test,
        s.transfer_commercial, s.transfer_anti, s.transfer_colost, s.transfer_total,
        s.loss, s.stock_commercial, s.stock_anti, s.colost, s.return_fresh, s.return_yogurt
        <foreach collection="feedSources" item="f" open="," separator="," close="">
            SUM(CASE WHEN fr.source_id = (SELECT id FROM np_milk_feed WHERE source_name = #{f}) THEN fr.amount ELSE NULL END) AS `${f}`
        </foreach>
        <foreach collection="saleDestinations" item="d" open="," separator="," close="">
            SUM(CASE WHEN sr.destination_id = (SELECT id FROM np_milk_sale WHERE destination_name = #{d}) THEN sr.amount ELSE NULL END) AS `${d}`
        </foreach>
        FROM np_milk_in_out_store s
        LEFT JOIN np_milk_feed_records fr ON fr.store_id = s.id
        LEFT JOIN np_milk_sale_records sr ON sr.store_id = s.id
        <where>
            <if test="queryParams.params.beginTime != null and queryParams.params.beginTime != ''">
                AND s.datetime &gt;= #{queryParams.params.beginTime}
            </if>
            <if test="queryParams.params.endTime != null and queryParams.params.endTime != ''">
                AND s.datetime &lt;= #{queryParams.params.endTime}
            </if>

            ${queryParams.params.dataScope}
        </where>
        GROUP BY s.id
        ORDER BY s.datetime
    </select>

    <select id="selectFeedSources" resultType="string">SELECT source_name FROM np_milk_feed</select>
    <select id="selectSaleDestinations" resultType="string">SELECT destination_name FROM np_milk_sale</select>

    <insert id="insertStore" useGeneratedKeys="true" keyProperty="id" parameterType="com.zhyc.module.dairyProducts.domain.NpMilkInOutStore">
        INSERT INTO np_milk_in_out_store(datetime, num, colost_sheep, commercial_intake, anti_intake,
                                         colost_intake, intake_total, commercial_test, colost_test, transfer_commercial,
                                         transfer_anti, transfer_colost, transfer_total, loss, stock_commercial,
                                         stock_anti, colost, return_fresh, return_yogurt, user_id, dept_id)
        VALUES (
                   #{datetime}, #{num}, #{colostSheep}, #{commercialIntake}, #{antiIntake},
                   #{colostIntake}, #{intakeTotal}, #{commercialTest}, #{colostTest},
                   #{transferCommercial}, #{transferAnti}, #{transferColost}, #{transferTotal},
                   #{loss}, #{stockCommercial}, #{stockAnti}, #{colost}, #{returnFresh}, #{returnYogurt}, #{userId}, #{deptId}
               )
    </insert>

    <insert id="insertFeedRecord">
        INSERT INTO np_milk_feed_records(store_id, source_id, amount, user_id, dept_id)
        VALUES(#{storeId},
               (SELECT id FROM np_milk_feed WHERE source_name = #{source}),
               #{amount}, #{userId}, #{deptId})
    </insert>

    <insert id="insertSaleRecord">
        INSERT INTO np_milk_sale_records(store_id, destination_id, amount, user_id, dept_id)
        VALUES(#{storeId},
               (SELECT id FROM np_milk_sale WHERE destination_name = #{destination}),
               #{amount}, #{userId}, #{deptId})
    </insert>

</mapper>