<?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.bonus.canteen.core.menu.mapper.MenuRecipeDishesMapper">
    <insert id="insertValues">
        INSERT INTO menu_recipe_dishes
        (detail_id, dishes_id, price, size_type, supply_num, sale_num, surplus_num,
        restrict_num, sale_price, recommend_flag, crby, crtime, upby, uptime, sort_num)
        VALUES
        <foreach collection="list" item="item" separator="," open="" close="">
           ( #{item.detailId}, #{item.dishesId},#{item.price},#{item.sizeType},#{item.supplyNum},#{item.saleNum},#{item.surplusNum},
            #{item.restrictNum},#{item.salePrice},#{item.recommendFlag}
            ,#{item.crby},#{item.crtime},#{item.upby},#{item.uptime},#{item.sortNum})
        </foreach>
    </insert>

    <!-- 获取指定菜品的历史销量 -->
    <select id="selectHistoricalSalesByDishesId" resultType="java.lang.Integer">
        select sum(sale_num)
        from menu_recipe_dishes mrd
                 left join menu_recipe_detail mrdd on mrdd.detail_id = mrd.detail_id
        where dishes_id = #{dishesId}
          and #{lastDay} >= mrdd.apply_date
          and mrdd.apply_date >= #{firstDay}
    </select>

    <select id="selectExistLeftJoinByDishesId" resultType="java.lang.Integer">
        select
            1
        from
            menu_recipe_detail mrd
                left join menu_recipe_dishes mrdd on mrd.detail_id = mrdd.detail_id
        where
            mrd.apply_date >= curdate()
            and mrdd.dishes_id in
        <foreach collection="dishesIdList" item="dishesId" open="(" separator="," close=")">
            #{dishesId}
        </foreach>
        limit 1
    </select>
    <select id="getDishesCountByRecipeIds" resultType="com.bonus.canteen.core.menu.dto.RecipeDishesCountDto">
        SELECT
        mrd.recipe_id,count(DISTINCT(md.base_dishes_id)) as num
        FROM
        menu_recipe_detail mrd
        LEFT JOIN menu_recipe_dishes mrdd ON mrd.detail_id = mrdd.detail_id
        LEFT JOIN menu_dishes md ON md.dishes_id = mrdd.dishes_id
        where
        mrd.apply_date >= curdate()
        <if test="recipeIds !=null and recipeIds.size()>0">
            AND mrd.recipe_id in
            <foreach collection="recipeIds" separator="," open="(" close=")" item="item">
                #{item}
            </foreach>
        </if>
        GROUP BY mrd.recipe_id;
    </select>

    <select id="queryDishesByDate" resultType="com.bonus.canteen.core.menu.dto.ExistDateDto">
        SELECT t2.apply_date,
        t2.mealtime_type
        FROM menu_recipe_dishes t1
        JOIN menu_recipe_detail t2 ON t1.detail_id = t2.detail_id
        WHERE t2.detail_type = 2
        AND t2.recipe_id = #{recipeId}
        AND t1.dishes_id = #{dishesId}
        AND t2.apply_date IN
        <foreach collection="dates" item="date" open="(" close=")" separator=",">
            #{date}
        </foreach>
    </select>
</mapper>
