<?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.core.menu.mapper.MenuEvaluaOrderMapper">

    <!-- 分页获取指定餐品的评价列表 -->
    <select id="selectAllListByMeal" resultType="com.bonus.core.menu.vo.MenuEvaluaOrderPageMealVO">
        select
        ci.cust_photo_url,
        ci.cust_name,
        meo.evalua_id,
        meo.ord_id,
        med.star_level,
        meo.description,
        meo.reply,
        meo.reply_time,
        meo.crtime as evalua_time
        from
        menu_evalua_detail med
        left join menu_dishes md on med.meal_id = md.dishes_id
        left join menu_evalua_order meo on med.evalua_id = meo.evalua_id
        left join order_info oai on meo.ord_id = oai.order_id
        left join cust_info ci on oai.cust_id = ci.cust_id
        left join menu_evalua_picture mep on meo.evalua_id = mep.evalua_id
        left join menu_material mm on mm.material_id=med.meal_id
        where
        meo.del_flag = 2
        and meo.show_flag = 1
        and (md.base_dishes_id = #{mealId} or mm.material_id=#{mealId})
        and meo.shopstall_id = #{shopstallId}
        <if test="searchType == 3">
            and meo.crtime between date_sub(now(), interval 30 day) and now()
        </if>
        group by
        ci.cust_photo_url,
        ci.cust_name,
        meo.evalua_id,
        meo.ord_id,
        med.star_level,
        meo.description,
        meo.reply,
        meo.reply_time,
        meo.crtime
        <trim prefix="having"  prefixOverrides="and" >
            <if test='searchType == 2'>
                and count(mep.id) > 0
            </if>
        </trim>
        order by
        meo.crtime desc
    </select>

    <select id="selectAvgByMealAndShop" resultType="com.bonus.core.menu.vo.MenuEvaluaMealVO">
        select
            md.base_dishes_id as meal_id,
            avg(med.star_level) as avg_star_level
          from
              menu_evalua_detail med
                  left join menu_evalua_order meo on med.evalua_id = meo.evalua_id
                  left join menu_dishes md on med.meal_id = md.dishes_id
                  left join menu_material mm on mm.material_id=med.meal_id
         where
             meo.del_flag = #{delFlag}
           and meo.show_flag = 1
           and (md.base_dishes_id = #{mealId} or mm.material_id=#{mealId})
           and med.meal_type = #{mealType}
           and meo.shopstall_id = #{shopstallId}
         group by
             md.base_dishes_id
    </select>

    <!-- 获取订单评价分页数据 -->
    <select id="selectEvaluatePagePc" resultType="com.bonus.core.menu.vo.MenuEvaluateOrderPageVO">
        select
        meo.evalua_id,
        oai.order_id as ord_id,
        ci.cust_name,
        oai.canteen_name,
        oai.stall_name,
        oai.mealtime_name,
        meo.description,
        meo.reply,
        meo.reply_time,
        meo.crtime,
        meo.show_flag,
        ar.area_name
        from
        menu_evalua_order meo
        left join order_info oai on meo.ord_id = oai.order_id
        left join cust_info ci on oai.cust_id = ci.cust_id
        LEFT JOIN alloc_canteen ac ON ac.canteen_id = oai.canteen_id
        LEFT JOIN alloc_area ar ON ar.area_id = ac.area_id
        where
        meo.del_flag = 2
        <if test="content.startTime != null">
            and meo.crtime >= #{content.startTime}
        </if>
        <if test="content.orderEvaluaType != null">
            and meo.order_evalua_type = #{content.orderEvaluaType}
        </if>
        <if test="content.endTime != null">
            and #{content.endTime} >= meo.crtime
        </if>
        <if test="content.keyword != null and content.keyword != ''">
            and (ci.cust_name like #{content.keyword} or meo.ord_id like #{content.keyword})
        </if>
        <if test="content.canteenIdList != null and content.canteenIdList.size() > 0">
            AND oai.canteen_id IN
            <foreach collection="content.canteenIdList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="content.stallIdList != null and content.stallIdList.size() > 0">
            AND oai.stall_id IN
            <foreach collection="content.stallIdList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="content.mealtimeType != null">
            and oai.mealtime_type = #{content.mealtimeType}
        </if>
        <if test="content.custId != null">
            and ci.cust_id = #{content.custId}
        </if>
        <if test="content.showFlag != null">
            and meo.show_flag = #{content.showFlag}
        </if>
        <if test="content.haveImage != null">
            and EXISTS( SELECT NULL FROM menu_evalua_picture mep where  mep.evalua_id = meo.evalua_id)
        </if>
        order by
        meo.crtime desc
    </select>

</mapper>
