<?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="org.springblade.modules.finance.mapper.OrderGrouponMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="orderGrouponResultMap" type="org.springblade.modules.finance.entity.OrderGroupon">
        <result column="id" property="id"/>
        <result column="is_delete" property="isDelete"/>
        <result column="create_user_id" property="createUserId"/>
        <result column="create_time" property="createTime"/>
        <result column="update_user_id" property="updateUserId"/>
        <result column="update_time" property="updateTime"/>
        <result column="order_id" property="orderId"/>
        <result column="self_take_id" property="selfTakeId"/>
        <result column="groupon_id" property="grouponId"/>
        <result column="organizer_id" property="organizerId"/>
        <result column="pickup_code" property="pickupCode"/>
    </resultMap>


    <select id="selectOrderGrouponPage" resultMap="orderGrouponResultMap">
        select * from t_order_groupon where is_delete = 0
    </select>
    <select id="getByGrouponIds" resultType="org.springblade.modules.finance.entity.OrderGroupon">
        SELECT
            og.*
        FROM
            t_order_groupon og
            JOIN t_order o on og.order_id = o.id
        WHERE
        o.order_state not in (1,5,7)
        AND og.groupon_id in
        <foreach collection="grouponIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <select id="getByGrouponId" resultType="org.springblade.modules.finance.entity.OrderGroupon">
        SELECT
            og.*
        FROM
            t_order_groupon og
            JOIN t_order o on og.order_id = o.id
        WHERE
        o.order_state not in (1,5)
        AND og.groupon_id = #{grouponId}
        <if test="selfTakeId != null">
            and og.self_take_id = #{selfTakeId}
        </if>
        <if test="state != 0">
            <choose>
                <when test="state == 1">
                    and og.is_arrive = 0
                </when>
                <otherwise>
                    AND o.order_state = #{state}
                </otherwise>
            </choose>
        </if>
    </select>
    <select id="getGrouponOrder" resultType="org.springblade.modules.finance.entity.OrderGroupon">
        SELECT
            og.*
        FROM
            t_order_groupon og
        JOIN t_order o on og.order_id = o.id
        WHERE
            o.order_state not in (1,5)
            AND og.groupon_id in
        <foreach collection="grouponIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="state != 0">
            <choose>
                <when test="state == 1">
                    and og.is_arrive = 0
                </when>
                <otherwise>
                    AND o.order_state = #{state}
                </otherwise>
            </choose>
        </if>

    </select>
    <select id="selectPickUpAppointment" resultType="org.springblade.modules.groupon.vo.AppointmentVO">
        SELECT
            g.id grouponId,
            g.main_title,
            g.groupon_start_time,
            g.groupon_end_time,
            (CASE
                    WHEN sum( og.is_charge_off = 1 ) = count(1) THEN 2
                    WHEN sum( og.is_arrive = 1 ) = count(1) THEN 1
                    ELSE 0
             END) state
        FROM
            t_order_groupon og
            JOIN t_groupon g ON og.groupon_id = g.id
            JOIN t_order o on og.order_id = o.id
        WHERE
             o.order_state not in (1,5)
            <if test="selfTakeId != null">
                and og.self_take_id = #{selfTakeId}
            </if>
            <if test="organizerUserId != null">
                and og.organizer_id = #{organizerUserId}
            </if>
        GROUP BY g.id
        <if test="state != null">
            HAVING state = #{state}
        </if>
        ORDER BY g.groupon_start_time
    </select>
    <select id="selectPickUpAppointmentData"
            resultType="org.springblade.modules.groupon.vo.ExportPickUpAppointmentVO">
        SELECT
            u.`name` username,
            u.phone,
            og.pickup_code pickUpCode,
            o.order_no orderNo,
            o.user_id userId,
            og.order_id orderId,
            ( CASE WHEN og.is_arrive = 1 THEN '已到货' ELSE '未到货' END ) arrivedDesc,
            ( CASE WHEN og.is_charge_off = 1 THEN '已核销' ELSE '未核销' END ) chargeOffDesc
        FROM
            t_order_groupon og
            JOIN t_order o ON og.order_id = o.id
            JOIN blade_user u ON o.user_id = u.id
        WHERE
            o.order_state NOT IN (1,5,7) and og.groupon_id = #{grouponId}
    </select>
    <select id="selectPickUpAppointmentDataAll"
            resultType="org.springblade.modules.groupon.vo.ExportAppointmentVO">
        SELECT
            st.`name` selfTakeName,
            st.address selfTakeAddress,
            st.contact_person selfTakePerson,
            st.contact_number selfTakePhone,
            IFNULL( o.distribute_mode, '顾客自提' ) distributionMode,
            u.`name` orderUser,
            u.phone orderUserPhone,
            uu.`name` commander,
            og.pickup_code pickUpCode,
            o.order_no orderNo,
            s.`name` storeName,
            ogg.goods_name goodsName,
            ogg.goods_spec goodsSpec,
            ogg.goods_no goodsNo,
            (ogg.goods_num - ogg.refund_goods_num_total) goodsNum,
            gi.cost_price costPrice,
            ggoods.groupon_price grouponPrice,
            IFNULL( a.return_goods_num, 0 ) refundGoodsNum,
            o.order_state orderState,
            o.user_id userId
        FROM
            t_order_groupon_goods ogg
            JOIN t_order_groupon og ON ogg.order_id = og.order_id
            JOIN t_groupon_goods ggoods ON og.groupon_id = ggoods.groupon_id
            JOIN t_goods_info gi ON ogg.goods_id = gi.id
            JOIN t_order o ON ogg.order_id = o.id
            JOIN t_self_take st ON og.self_take_id = st.id
            JOIN t_store s ON ogg.supplier_id = s.id
            JOIN blade_user u ON o.user_id = u.id
            JOIN blade_user uu ON og.organizer_id = uu.id
            LEFT JOIN t_after_sales a ON ogg.id = a.order_groupon_goods_id
        WHERE
            ggoods.goods_id = ogg.goods_id
            AND o.order_state NOT IN ( 1, 5 )
            AND og.groupon_id = #{grouponId}
        HAVING goodsNum > 0
    </select>

</mapper>
