<?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.cook.mapper.CookRecipeMapper">
    <resultMap type="com.bonus.canteen.core.cook.domain.CookRecipe" id="CookRecipeResult">
        <result property="recipeId"    column="recipe_id"    />
        <result property="recipeName"    column="recipe_name"    />
        <result property="recipeType"    column="recipe_type"    />
        <result property="stallId"    column="stall_id"    />
        <result property="stallName"    column="stall_name"    />
        <result property="canteenId"    column="canteen_id"    />
        <result property="canteenName"    column="canteen_name"    />
        <result property="areaId"    column="area_id"    />
        <result property="areaName"    column="area_name"    />
        <result property="planId"    column="plan_id"    />
        <result property="effectDate"    column="effect_date"    />
        <result property="expireDate"    column="expire_date"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <!-- 获取指定日期菜谱详情 -->
    <resultMap id="cookRecipeDetailVO" type="com.bonus.canteen.core.cook.vo.CookRecipeDetailVO">
        <result property="mealtimeType" column="mealtime_type"/>
        <result property="recipeDetailId" column="recipe_detail_id"/>
        <collection property="dishesList" ofType="com.bonus.canteen.core.cook.vo.CookRecipeDishesVO">
            <result property="id" column="id"/>
            <result property="dishesId" column="dishes_id"/>
            <result property="dishesName" column="dishes_name"/>
            <result property="price" column="price"/>
            <result property="sizeType" column="size_type"/>
            <result property="supplyNum" column="supply_num"/>
            <result property="saleNum" column="sale_num"/>
            <result property="remanentNum" column="remanent_num"/>
            <result property="limitNum" column="limit_num"/>
            <result property="salePrice" column="sale_price"/>
            <result property="recommendFlag" column="recommend_flag"/>
            <result property="canteenName" column="canteen_name"/>
            <result property="salesMode" column="sales_mode"/>
            <result property="unitPrice" column="unit_price"/>
            <result property="mealType" column="meal_type"/>
            <result property="materialCost" column="material_cost"/>
            <result property="typeId" column="type_id"/>
            <result property="dishesTypeName" column="dishes_type_name"/>
        </collection>
    </resultMap>

    <!-- 获取指定日期菜谱详情 -->
    <resultMap id="cookRecipeDateVO" type="com.bonus.canteen.core.cook.vo.CookRecipeDateVO">
        <result property="day" column="apply_date"/>
        <result property="recipeId" column="recipe_id"/>
        <collection property="detailList" ofType="com.bonus.canteen.core.cook.vo.CookRecipeDetailVO">
            <result property="mealtimeType" column="mealtime_type"/>
            <result property="recipeDetailId" column="recipe_detail_id"/>
            <collection property="dishesList" ofType="com.bonus.canteen.core.cook.vo.CookRecipeDishesVO">
                <result property="dishesId" column="dishes_id"/>
                <result property="dishesName" column="dishes_name"/>
                <result property="dishesId" column="dishesId"/>
                <result property="price" column="price"/>
                <result property="sizeType" column="size_type"/>
                <result property="supplyNum" column="supply_num"/>
                <result property="saleNum" column="sale_num"/>
                <result property="remanentNum" column="remanent_num"/>
                <result property="limitNum" column="limit_num"/>
                <result property="salePrice" column="sale_price"/>
                <result property="recommendFlag" column="recommend_flag"/>
                <result property="typeId" column="type_id"/>
                <result property="dishesTypeName" column="dishes_type_name"/>
                <result property="salesMode" column="sales_mode"/>
                <result property="canteenName" column="canteen_name"/>
                <result property="unitPrice" column="unit_price"/>
                <result property="mealType" column="meal_type"/>
                <result property="materialCost" column="material_cost"/>
            </collection>
        </collection>
    </resultMap>

    <sql id="selectCookRecipeVo">
        select cr.recipe_id, cr.recipe_name, cr.recipe_type, cr.stall_id, cr.canteen_id, cr.plan_id, cr.effect_date,
               cr.expire_date, cr.create_by, cr.create_time, cr.update_by, cr.update_time, cr.del_flag,
               bs.stall_name, bc.canteen_name, ba.area_name, ba.area_id
        from cook_recipe cr
        left join basic_stall bs on bs.stall_id = cr.stall_id
        left join basic_canteen bc on bc.canteen_id = bs.canteen_id
        left join basic_area ba on ba.area_id = bc.area_id
    </sql>

    <select id="selectCookRecipeList" parameterType="com.bonus.canteen.core.cook.domain.CookRecipe" resultMap="CookRecipeResult">
        <include refid="selectCookRecipeVo"/>
        <where>
            ba.del_flag = '0' and bc.del_flag = '0' and bs.del_flag = '0' and cr.del_flag = '0'
            <if test="recipeName != null  and recipeName != ''"> and cr.recipe_name like concat('%', #{recipeName}, '%')</if>
            <if test="recipeType != null "> and cr.recipe_type = #{recipeType}</if>
            <if test="stallId != null "> and cr.stall_id = #{stallId}</if>
            <if test="canteenId != null "> and cr.canteen_id = #{canteenId}</if>
            <if test="planId != null  and planId != ''"> and cr.plan_id = #{planId}</if>
            <if test="effectDate != null "> and cr.effect_date = #{effectDate}</if>
            <if test="expireDate != null "> and cr.expire_date = #{expireDate}</if>
            <if test="delFlag != null  and delFlag != ''"> and cr.del_flag = #{delFlag}</if>
        </where>
        order by cr.create_time desc
    </select>

    <select id="selectRecipeListByRecipeIds" resultType="com.bonus.canteen.core.cook.domain.CookRecipe">
        <include refid="selectCookRecipeVo"/>
        <where>
            cr.del_flag = '0'
            <if test="recipeIds !=null and recipeIds.size()>0">
                AND cr.recipe_id in
                <foreach collection="recipeIds" open="(" close=")" item="item" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectRecipeListByRecipeTypes" resultType="com.bonus.canteen.core.cook.domain.CookRecipe">
        <include refid="selectCookRecipeVo"/>
        where cr.recipe_type in (2,3) and cr.del_flag = '0'
    </select>

    <select id="selectRecipeListByRecipeTypesAndRecipeIds" resultType="com.bonus.canteen.core.cook.domain.CookRecipe">
        <include refid="selectCookRecipeVo"/>
        where cr.recipe_type in (2,3) and cr.del_flag = '0' and cr.recipe_id in
        <foreach collection="recipeIds" open="(" close=")" item="item" separator=",">
            #{item}
        </foreach>
    </select>

    <select id="getDishesCount4PointDates" resultType="Integer">
        select count(1)
        from cook_recipe_dishes where recipe_detail_id in
            (select recipe_detail_id from cook_recipe_detail where recipe_id = #{recipeId} and apply_date >= NOW()-1)
    </select>

    <select id="getDishesCount4Recycle" resultType="Integer">
        select count(1) from cook_recipe_dishes where recipe_detail_id in
            (select recipe_detail_id from cook_recipe_detail where recipe_id = #{recipeId} and detail_type = 1)
    </select>

    <select id="getDishesCountByRecipeIds" resultType="com.bonus.canteen.core.cook.vo.CookRecipeDishesCountVO">
        SELECT
        mrd.recipe_id,count(DISTINCT(md.dishes_id)) as count
        FROM
        cook_recipe_detail mrd
        LEFT JOIN cook_recipe_dishes mrdd ON mrd.recipe_detail_id = mrdd.recipe_detail_id
        LEFT JOIN cook_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="selectApplyDateListByRecipeId" resultType="com.bonus.canteen.core.cook.vo.CookRecipeDetailVO">
        SELECT recipe_id as recipeId, apply_date as applyDate
        FROM cook_recipe_detail
        WHERE apply_date >= #{applyDate}
        <if test="recipeIds !=null and recipeIds.size()>0">
            AND recipe_id in
            <foreach collection="recipeIds" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <!-- 获取指定日期菜谱详情 -->
    <select id="selectRecipeDetailList" resultMap="cookRecipeDetailVO">
        select crd.mealtime_type,
               crd.recipe_detail_id,
               cd.dishes_id,
               cd.dishes_name,
               crdd.id,
               crdd.price,
               crdd.size_type,
               crdd.supply_num,
               crdd.sale_num,
               (crdd.supply_num - crdd.sale_num) as remanent_num,
               crdd.limit_num,
               crdd.sale_price,
               crdd.recommend_flag,
               bc.canteen_name,
               cd.sales_mode,
               cd.unit_price,
               cd.meal_type,
               cd.type_id,
               cdt.dishes_type_name,
               cd.material_cost
        from cook_recipe_detail crd
                 left join cook_recipe_dishes crdd on crd.recipe_detail_id = crdd.recipe_detail_id
                 left join cook_dishes cd on crdd.dishes_id = cd.dishes_id
                 left join basic_canteen bc on cd.canteen_id = bc.canteen_id
                 left join cook_dishes_type cdt on cd.type_id = cdt.dishes_type_id
        where crd.recipe_id = #{recipeId}
          and crd.apply_date = #{applyDate}
        order by crdd.dishes_id
    </select>

    <select id="getRecipeDetail" resultMap="cookRecipeDetailVO">
        select DISTINCT
            mrd.mealtime_type,
            mrd.recipe_detail_id,
            md.dishes_id,
            md.dishes_name,
            mrdd.id,
            mrdd.price,
            mrdd.size_type,
            mrdd.supply_num,
            mrdd.sale_num,
            mrdd.remanent_num,
            mrdd.limit_num,
            mrdd.sale_price,
            mrdd.recommend_flag,
            ac.canteen_name,
            md.sales_mode,
            md.unit_price,
            md.meal_type,
            md.type_id,
            mdt.dishes_type_name,
            md.material_cost
        from cook_recipe_detail mrd
        left join cook_recipe_dishes mrdd on mrd.recipe_detail_id = mrdd.recipe_detail_id
        left join cook_dishes md on mrdd.dishes_id = md.dishes_id
        left join basic_canteen ac on md.canteen_id = ac.canteen_id
        left join cook_dishes_type mdt on md.type_id = mdt.dishes_type_id
        where mrd.recipe_id = #{params.recipeId}
        <if test="params.applyWeek !=null and params.applyWeek != ''">
            and mrd.apply_week = #{params.applyWeek}
        </if>
        and mrd.detail_type = 1
        order by mrdd.dishes_id
    </select>

    <select id="selectTemplateDetailList" resultMap="cookRecipeDetailVO">
        select mrd.mealtime_type,
            mrd.recipe_detail_id,
            md.dishes_id,
            md.dishes_name,
            mrdd.id,
            mrdd.price,
            mrdd.size_type,
            mrdd.supply_num,
            mrdd.sale_num,
            mrdd.remanent_num,
            mrdd.limit_num,
            mrdd.sale_price,
            mrdd.recommend_flag,
            ac.canteen_name,
            md.sales_mode,
            md.unit_price,
            md.meal_type,
            md.type_id,
            mdt.dishes_type_name,
            md.material_cost
        from cook_recipe_detail mrd
        left join cook_recipe_dishes mrdd on mrd.recipe_detail_id = mrdd.recipe_detail_id
        left join cook_dishes md on mrdd.dishes_id = md.dishes_id
        left join basic_canteen ac on md.canteen_id = ac.canteen_id
        left join cook_dishes_type mdt on md.type_id = mdt.dishes_type_id
        where mrd.recipe_id = #{params.recipeId}
        <if test="params.applyWeek !=null ">
            and mrd.apply_week = #{params.applyWeek}
        </if>
        and mrd.detail_type = 1
        order by mrdd.dishes_id
    </select>


    <select id="selectCookRecipeByRecipeId" parameterType="Long" resultMap="CookRecipeResult">
        <include refid="selectCookRecipeVo"/>
        where cr.recipe_id = #{recipeId} and ba.del_flag = '0' and bc.del_flag = '0' and bs.del_flag = '0' and cr.del_flag = '0'
    </select>

    <select id="selectCookRecipeByRecipeName" parameterType="String" resultMap="CookRecipeResult">
        <include refid="selectCookRecipeVo"/>
        where cr.recipe_name = #{recipeName} and ba.del_flag = '0' and bc.del_flag = '0' and bs.del_flag = '0' and cr.del_flag = '0'
    </select>

    <insert id="insertCookRecipe" parameterType="com.bonus.canteen.core.cook.domain.CookRecipe" useGeneratedKeys="true" keyProperty="recipeId">
        insert into cook_recipe
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="recipeName != null and recipeName != ''">recipe_name,</if>
            <if test="recipeType != null">recipe_type,</if>
            <if test="stallId != null">stall_id,</if>
            <if test="canteenId != null">canteen_id,</if>
            <if test="planId != null">plan_id,</if>
            <if test="effectDate != null">effect_date,</if>
            <if test="expireDate != null">expire_date,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="recipeName != null and recipeName != ''">#{recipeName},</if>
            <if test="recipeType != null">#{recipeType},</if>
            <if test="stallId != null">#{stallId},</if>
            <if test="canteenId != null">#{canteenId},</if>
            <if test="planId != null">#{planId},</if>
            <if test="effectDate != null">#{effectDate},</if>
            <if test="expireDate != null">#{expireDate},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateCookRecipe" parameterType="com.bonus.canteen.core.cook.domain.CookRecipe">
        update cook_recipe
        <trim prefix="SET" suffixOverrides=",">
            <if test="recipeName != null and recipeName != ''">recipe_name = #{recipeName},</if>
            <if test="recipeType != null">recipe_type = #{recipeType},</if>
            <if test="stallId != null">stall_id = #{stallId},</if>
            <if test="canteenId != null">canteen_id = #{canteenId},</if>
            <if test="planId != null">plan_id = #{planId},</if>
            <if test="effectDate != null">effect_date = #{effectDate},</if>
            <if test="expireDate != null">expire_date = #{expireDate},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where recipe_id = #{recipeId}
    </update>

    <select id="getDeviceIdByDevice" parameterType="string" resultType="integer">
        select a.device_id
        from device_info a
                 LEFT JOIN cook_recipe_bind_device b on a.device_id = b.device_id
        where  b.stall_id = #{stallId}
    </select>

    <select id="getDeviceSnByIds" resultType="java.lang.String">
        select device_sn
        from device_info
        where device_id in
        <foreach collection="list" item="deviceId" separator="," open="(" close=")">
            #{deviceId}
        </foreach>
    </select>

    <select id="checkIfAlreadyBind" resultType="boolean">
        select count(1) > 0 from cook_recipe_bind_app
        where recipe_id = #{recipeId}
        <if test="mealLineId !=null">
            and meal_line_id = #{mealLineId}
        </if>
        and bind_type = #{bindType}
    </select>

    <select id="selectRecipeInSameShop" resultType="java.lang.Long">
        select mar.recipe_id
        from cook_recipe_bind_app mar
        inner join cook_recipe mr on mar.recipe_id = mr.recipe_id
        where mar.bind_type = #{bindType}
        and mr.stall_id = #{stallId}
        <if test="mealLineId !=null">
            and mar.meal_line_id= #{mealLineId}
        </if>
    </select>

    <delete id="deleteBind" parameterType="com.bonus.canteen.core.cook.domain.CookAppRecipe">
        delete from cook_recipe_bind_app where recipe_id in
        <foreach collection="recipeIds" item="recipeId" separator="," open="(" close=")">
            #{recipeId}
        </foreach>
        <if test="mealLineId != null">
            and meal_line_id = #{mealLineId}
        </if>
        and bind_type = #{bindType}
    </delete>

    <insert id="insertBind" parameterType="com.bonus.canteen.core.cook.domain.CookAppRecipe">
        insert into cook_recipe_bind_app (recipe_id,bind_type,bind_time,meal_line_id)
        values (#{recipeId},#{bindType},now(),#{mealLineId})
    </insert>

    <update id="updateRecipeByCanteenStallMeal">
        update cook_recipe_bind_device  set recipe_id = #{ recipeId}
        where canteen_id = #{canteenId} and stall_id = #{stallId}
        and device_id in
        <foreach collection="deviceIds" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
    </update>

    <delete id="deleteCookRecipeByRecipeId" parameterType="Long">
        update cook_recipe set del_flag = '2' where recipe_id = #{recipeId}
    </delete>

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


    <select id="getCookRecipeByRecipeIds"  resultMap="CookRecipeResult">
       select  * from cook_recipe where recipe_id in
        <foreach item="recipeId" collection="recipeIds" open="(" separator="," close=")">
            #{recipeId}
        </foreach>
        and del_flag = 0
    </select>

    <select id="selectCookRecipeListV2" resultType="com.bonus.canteen.core.cook.vo.AllocRecipeStallVO">
        SELECT
        t3.area_id, t3.area_name, t2.canteen_id, t2.canteen_name, t1.stall_id, t1.stall_name
        FROM
        basic_stall t1
        LEFT JOIN basic_canteen t2 ON t1.canteen_id = t2.canteen_id
        LEFT JOIN basic_area t3 ON t2.area_id = t3.area_id
        where t1.del_flag = '0' and t2.del_flag = '0' and t3.del_flag = '0'
        <if test="recipeName !=null and recipeName !=''">
            AND EXISTS (
            select null
            from cook_recipe_bind_app t4 INNER JOIN  cook_recipe t5 on  t4.recipe_id = t5.recipe_id
            where t5.del_flag = '0' and t5.stall_id = t1.stall_id  AND t4.bind_type = #{bindType}
            and t5.recipe_name like #{recipeName}
            )
        </if>
        <if test="deviceTypeList != null and deviceTypeList.size() > 0">
            AND EXISTS (
            SELECT NULL
            FROM
            cook_recipe_bind_device t6
            LEFT JOIN device_info t7 ON t7.device_id = t6.device_id
            WHERE
            t6.canteen_id = t2.canteen_id
            AND t6.stall_id = t1.stall_id
            AND t7.device_type IN
            <foreach collection="deviceTypeList" item="deviceType" separator="," open="(" close=")">
                #{deviceType}
            </foreach>
            )
        </if>
        <if test="areaId !=null">
            and t3.area_id  = #{areaId}
        </if>
        <if test="canteenId !=null">
            and t2.canteen_id  = #{canteenId}
        </if>
        <if test="stallId !=null ">
            and t1.stall_id = #{stallId}
        </if>
    </select>

    <select id="selectRecipeByStallIdsAndBindType"
            resultType="com.bonus.canteen.core.cook.dto.StallAndRecipeBindDto">
        select
        crba.recipe_id,
        cr.stall_id,
        crba.bind_type
        from cook_recipe_bind_app crba
        left join cook_recipe cr on crba.recipe_id = cr.recipe_id
        where bind_type = #{bindType} and cr.del_flag = '0'
        <if test="stallIds !=null and stallIds.size() > 0 ">
            and cr.stall_id IN
            <foreach collection="stallIds" item="stallId" separator="," open="(" close=")">
                #{stallId}
            </foreach>
        </if>
    </select>

    <select id="selectTmpBetweenDateDetailList" resultMap="cookRecipeDateVO">
        select mrd.mealtime_type,
                mrd.recipe_id,
                mrd.recipe_detail_id,
                md.dishes_id,
                md.dishes_name,
                mrdd.id,
                mrdd.price,
                mrdd.size_type,
                mrdd.supply_num,
                mrdd.sale_num,
                mrdd.remanent_num,
                mrdd.limit_num,
                mrdd.sale_price,
                mrdd.recommend_flag,
                md.sales_mode,
                md.unit_price,
                md.meal_type,
                ac.canteen_name,
                md.material_cost,
                md.type_id,
                mdt.dishes_type_name
        from cook_recipe_detail mrd
        left join cook_recipe_dishes mrdd on mrd.recipe_detail_id = mrdd.recipe_detail_id
        left join cook_dishes md on mrdd.dishes_id = md.dishes_id
        left join basic_canteen ac on md.canteen_id = ac.canteen_id
        left join cook_dishes_type mdt on md.type_id = mdt.dishes_type_id
        where 1=1
        <if test="recipeIds !=null and recipeIds.size() > 0">
            and mrd.recipe_id in
            <foreach collection="recipeIds" item="recipeId" separator="," open="(" close=")">
                #{recipeId}
            </foreach>
        </if>
        and mrd.apply_date between #{applyStartDate} and #{applyEndDate}
        order by mrd.apply_date
    </select>
</mapper>

