<?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.czg.service.order.mapper.GbOrderMapper">

    <select id="countOrder" resultType="com.czg.order.vo.GbOrderCountVO">
        SELECT
        sum(CASE WHEN pay_time is not null THEN detail.pay_amount ELSE 0 END) AS paidAmountTotal,
        sum(CASE WHEN status ='已退款' THEN detail.pay_amount ELSE 0 END) AS refundAmount,
        count(detail.id) as countNum
        FROM
        `gb_order_detail` detail
        WHERE
        detail.shop_id = #{param.shopId}
        and detail.is_del = 0
        <if test="param.orderNo != null">
            and detail.order_no = #{param.orderNo}
        </if>
        <if test="param.groupOrderNo != null">
            AND detail.group_order_no = #{param.groupOrderNo}
        </if>
        <if test="param.status != null">
            <if test="param.status == '退款'">
                AND (detail.STATUS = '退款中' or detail.STATUS = '已退款')
            </if>
            <if test="param.status != '退款'">
                AND detail.STATUS = #{param.status}
            </if>
        </if>
        <if test="param.userId != null">
            AND detail.user_id = #{param.userId}
        </if>
        <if test="param.orderStartTime != null and param.orderEndTime != null ">
            and detail.pay_time BETWEEN #{param.orderStartTime} and #{param.orderEndTime}
        </if>
        <if test="param.verifyStartTime != null and param.verifyEndTime != null ">
            and detail.verify_time BETWEEN #{param.verifyStartTime} and #{param.verifyEndTime}
        </if>
    </select>

    <select id="getGbOrderPage" resultType="com.czg.order.vo.GbOrderDetailVO">
        SELECT
        detail.* ,`order`.ware_id as wareId,`order`.ware_json as wareJson,`order`.group_end_time as groupEndTime,
        `order`.group_people_num as groupPeopleNum,`order`.ware_group_price as wareGroupPrice,
        `order`.ware_original_price as wareOriginalPrice,`order`.status as wareGroupStatus,
        `user`.nick_name as userName,`user`.phone as userPhone,shop.shop_name as shopName,shop.address as shopAddress
        FROM
        `gb_order_detail` detail
        LEFT JOIN gb_order `order` on detail.group_order_no = `order`.group_order_no and `order`.shop_id = #{shopId}
        left join tb_user_info `user` on `user`.id = detail.user_id
        LEFT JOIN tb_shop_info shop on detail.shop_id = shop.id
        WHERE
        detail.shop_id = #{shopId}
        and detail.is_del = 0
        <if test="param.orderNo != null">
            and detail.order_no LIKE CONCAT('%', #{param.orderNo}, '%')
        </if>
        <if test="param.groupOrderNo != null">
            AND detail.group_order_no = #{param.groupOrderNo}
        </if>
        <if test="param.status != null">
            <if test="param.status == '退款'">
                AND (detail.STATUS = '退款中' or detail.STATUS = '已退款')
            </if>
            <if test="param.status != '退款'">
                AND detail.STATUS = #{param.status}
            </if>
        </if>
        <if test="param.userId != null">
            AND detail.user_id = #{param.userId}
        </if>
        <if test="param.orderStartTime != null and param.orderEndTime != null ">
            and detail.pay_time BETWEEN #{param.orderStartTime} and #{param.orderEndTime}
        </if>
        <if test="param.verifyStartTime != null and param.verifyEndTime != null ">
            and detail.verify_time BETWEEN #{param.verifyStartTime} and #{param.verifyEndTime}
        </if>
        order by
        <if test="param.status != null">
            <if test="param.status == '退款'">
                detail.`status` desc,detail.pay_time desc,
            </if>
        </if>
        detail.create_time desc
    </select>

    <select id="getDetailByDetailId" resultType="com.czg.order.vo.GbOrderDetailVO">
        SELECT
        detail.* ,`order`.ware_id as wareId,`order`.ware_json as wareJson,`order`.group_end_time as groupEndTime,
        `order`.group_people_num as groupPeopleNum,`order`.ware_group_price as wareGroupPrice,
        `order`.ware_original_price as wareOriginalPrice,`order`.status as wareGroupStatus,
        `user`.nick_name as userName,`user`.phone as userPhone,shop.shop_name as shopName,shop.address as shopAddress
        FROM
        `gb_order_detail` detail
        LEFT JOIN gb_order `order` on detail.group_order_no = `order`.group_order_no and `order`.shop_id = #{shopId}
        left join tb_user_info `user` on `user`.id = detail.user_id
        LEFT JOIN tb_shop_info shop on detail.shop_id = shop.id
        WHERE
        detail.shop_id = #{shopId} and detail.id=#{detailId} and detail.is_del = 0
        order by detail.create_time desc
    </select>

    <select id="getDetailByGroupNo" resultType="com.czg.order.vo.GbOrderDetailVO">
        SELECT
            detail.* ,`order`.ware_id as wareId,`order`.ware_json as wareJson,`order`.group_end_time as groupEndTime,
            `order`.group_people_num as groupPeopleNum,`order`.ware_group_price as wareGroupPrice,
            `order`.ware_original_price as wareOriginalPrice,`order`.status as wareGroupStatus,
            `user`.nick_name as userName,`user`.phone as userPhone,shop.shop_name as shopName,shop.address as shopAddress
        FROM
            gb_order `order`
            LEFT JOIN gb_order_detail detail on detail.group_order_no = `order`.group_order_no and `detail`.shop_id = #{shopId} and `detail`.user_id = #{userId} and `detail`.status != '已退款' and `detail`.is_del = 0
            LEFT JOIN tb_user_info `user` on `user`.id = detail.user_id
            LEFT JOIN tb_shop_info shop on `order`.shop_id = shop.id
        WHERE
            `order`.group_order_no = #{groupOrderNo}
    </select>

    <select id="getGbOrderDetailUsers" resultType="com.czg.order.vo.GbOrderUserVO">
        select `user`.nick_name as userName, `user`.phone as userPhone, `user`.head_img as userAvatar
        from gb_order_detail detail
                 left join tb_user_info `user` on `user`.id = detail.user_id
        where detail.shop_id = #{shopId}
          and detail.group_order_no = #{groupOrderNo}
          and (detail.status = '待成团' or detail.status = '待核销' or detail.status = '退款中')
        order by detail.pay_time
        limit 10
    </select>
</mapper>
