<?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.ruoyi.bst.order.mapper.OrderMapper">

    <resultMap type="OrderVO" id="OrderResult" autoMapping="true">
        <result property="id"    column="id"    />
        <result property="orderNo"    column="order_no"    />
        <result property="storeId"    column="store_id"    />
        <result property="storeName"    column="store_name"    />
        <result property="userId"    column="user_id"    />
        <result property="payId"    column="pay_id"    />
        <result property="payAmount"    column="pay_amount"    />
        <result property="mark"    column="mark"    />
        <result property="status"    column="status"    />
        <result property="createTime"    column="create_time"    />
        <result property="suitId"    column="suit_id"    />
        <result property="suitNum"    column="suit_num"    />
        <result property="suitAmount"    column="suit_amount"    />
        <result property="cancelRemark"    column="cancel_remark"    />
        <result property="payExpireTime"    column="pay_expire_time"    />
        <result property="refundNum"    column="refund_num"    />
    </resultMap>

    <sql id="selectOrderVo">
        select
            bo.id,
            bo.order_no,
            bo.store_id,
            bo.store_name,
            bo.user_id,
            bo.pay_id,
            bo.pay_amount,
            bo.mark,
            bo.status,
            bo.refund_num,
            bo.create_time,
            bo.suit_id,
            bo.suit_num,
            bo.suit_amount,
            bo.cancel_remark,
            bo.pay_expire_time,
            bp.refunded as pay_refunded,
            bp.refunding as pay_refunding,
            bp.no as pay_no,
            bp.amount as payed_amount,
            su.nick_name as user_name,
            bs.address,
            su.phonenumber
        from <include refid="searchTables"></include>
    </sql>

    <sql id="searchTables">
        bst_order bo
            left join sys_user su on bo.user_id = su.user_id
            left join bst_store bs on bo.store_id = bs.store_id
            left join bst_pay bp on bo.pay_id = bp.id
    </sql>

    <sql id="searchCondition">
        <if test="query.orderNo != null  and query.orderNo != ''"> and bo.order_no like concat('%', #{query.orderNo}, '%')</if>
        <if test="query.id != null  and query.id != ''"> and bo.id = #{query.id}</if>
        <if test="query.storeId != null "> and bo.store_id = #{query.storeId}</if>
        <if test="query.storeName != null  and query.storeName != ''"> and bo.store_name like concat('%', #{query.storeName}, '%')</if>
        <if test="query.userId != null "> and bo.user_id = #{query.userId}</if>
        <if test="query.payId != null "> and bo.pay_id = #{query.payId}</if>
        <if test="query.payNo != null  and query.payNo != ''"> and bp.no like concat('%', #{query.payNo}, '%')</if>
        <if test="query.payAmount != null "> and bo.pay_amount = #{query.payAmount}</if>
        <if test="query.mark != null  and query.mark != ''"> and bo.mark = #{query.mark}</if>
        <if test="query.status != null  and query.status != ''"> and bo.status = #{query.status}</if>
        <if test="query.suitId != null "> and bo.suit_id = #{query.suitId}</if>
        <if test="query.suitNum != null "> and bo.suit_num = #{query.suitNum}</if>
        <if test="query.suitAmount != null "> and bo.suit_amount = #{query.suitAmount}</if>
        <if test="query.cancelRemark != null  and query.cancelRemark != ''"> and bo.cancel_remark = #{query.cancelRemark}</if>
        <if test="query.payExpireTime != null "> and bo.pay_expire_time = #{query.payExpireTime}</if>
        <if test="query.beginTime != null"> and date(bo.create_time) &gt;= #{query.beginTime}</if>
        <if test="query.endTime != null"> and date(bo.create_time) &lt;= #{query.endTime}</if>
        <if test="query.createDate != null"> and date(bo.create_time) = #{query.createDate}</if>
        <if test="query.mchId != null"> and bs.user_id = #{query.mchId}</if>
        <if test="query.userName != null"> and su.user_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.statusList != null and query.statusList.size() > 0">
            and bo.status in
            <foreach collection="query.statusList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.storeIds != null and query.storeIds.size() > 0">
            and bo.store_id in
            <foreach item="item" collection="query.storeIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.keyword != null  and query.keyword != ''">
            and (
            bo.order_no like concat('%', #{query.keyword}, '%')
            )
        </if>
        <if test="query.endDateRange != null and query.endDateRange.size() > 1">
            and date(bo.create_time) &gt;= #{query.endDateRange[0]}
            and date(bo.create_time) &lt;= #{query.endDateRange[1]}
        </if>
        <if test="query.createDateRange != null and query.createDateRange.size() > 1">
            and date(bo.create_time) &gt;= #{query.createDateRange[0]}
            and date(bo.create_time) &lt;= #{query.createDateRange[1]}
        </if>
          ${@com.ruoyi.framework.util.DataScopeUtil@create(query.scope)
            .userSetAlias("bs.user_id")
              .storeAlias("bo.store_id",query.storePermissions)
            .build()
        }
        ${query.params.dataScope}
    </sql>

    <sql id="searchConditionForRefund">
        <include refid="searchCondition"/>
        <if test="query.refundStatusList != null and query.refundStatusList.size() > 0">
            and br.status in
            <foreach item="item" collection="query.refundStatusList" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.refundCreateDateRange != null and query.refundCreateDateRange.size() > 1">
            and date(br.create_time) &gt;= #{query.refundCreateDateRange[0]}
            and date(br.create_time) &lt;= #{query.refundCreateDateRange[1]}
        </if>
    </sql>

    <select id="selectOrderList" parameterType="OrderQuery" resultMap="OrderResult">
        <include refid="selectOrderVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by create_time desc
    </select>

    <select id="selectOrderById" parameterType="Long" resultMap="OrderResult">
        <include refid="selectOrderVo"/>
        where bo.id = #{id}
    </select>


    <select id="selectOrderByBstId" parameterType="Long" resultMap="OrderResult">
        <include refid="selectOrderVo"/>
        where bo.bst_id = #{bstId}
    </select>
    <!-- selectIdByQuery -->

    <select id="selectIdByQuery" resultType="Long">
        select bo.id
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectOrderCount" resultType="com.ruoyi.common.domain.vo.LocalDateTimeIntegerVO">
        select
        date(create_time) as `key`,
        count(bo.id) as `value`
        from bst_order bo
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectCountGroupByStoreId" resultType="com.ruoyi.common.domain.vo.LongIntegerVO">
            select
            bo.store_id as `key`,
            count(bo.id) as `value`
            from bst_order bo
            <where>
                <include refid="searchCondition"/>
            </where>
            group by `key`
    </select>

    <select id="selectUserIdsByStoreId" resultType="com.ruoyi.system.user.domain.vo.UserConsumeVO">
    select
        select bo.user_id
        form bst_order bo
        where bo.store_id = #{storeId}
    </select>

    <select id="selectSumOfAmountGroupByUserId" resultType="com.ruoyi.common.domain.vo.LongDecimalVO">
            select
            bo.user_id as `key`,
            sum(bo.pay_amount) as `value`
            from bst_order bo
            <where>
                <include refid="searchCondition"/>
            </where>
            group by `key`
    </select>

    <select id="selectUserCountGroupByStore" resultType="com.ruoyi.common.domain.vo.LongIntegerVO">
        select bo.store_id as `key`,
        count(distinct bo.user_id) as `value`
        from bst_order bo
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectSumOfActualTotalAmount" resultType="java.math.BigDecimal">
        select sum(bo.pay_amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>
    <select id="selectSumOfPayAmount" resultType="java.math.BigDecimal"></select>
    <select id="selectSumOfActualDispatchFee" resultType="java.math.BigDecimal"></select>
    <select id="selectSumOfActualManageFee" resultType="java.math.BigDecimal"></select>

    <select id="selectSumOfRefundAmount" resultType="java.math.BigDecimal">
        select sum(br.amount)
        from <include refid="searchTables"/>
        left join bst_refund br on br.pay_id = bp.id
        <where>
            <include refid="searchConditionForRefund"/>
        </where>
    </select>

    <select id="selectUserCount" resultType="java.lang.Integer">
        select count(distinct bo.user_id)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(bo.id)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- addRefundNum -->
    <insert id="addRefundNum">
        update bst_order
        set refund_num = refund_num + #{number}
        where pay_id = #{id} and suit_num >= refund_num + #{number}
    </insert>

    <insert id="insertOrder" parameterType="Order" useGeneratedKeys="true" keyProperty="id">
        insert into bst_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orderNo != null and orderNo != ''">order_no,</if>
            <if test="storeId != null">store_id,</if>
            <if test="storeName != null">store_name,</if>
            <if test="userId != null">user_id,</if>
            <if test="payId != null">pay_id,</if>
            <if test="payAmount != null">pay_amount,</if>
            <if test="mark != null">mark,</if>
            <if test="status != null">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="suitId != null">suit_id,</if>
            <if test="suitNum != null">suit_num,</if>
            <if test="suitAmount != null">suit_amount,</if>
            <if test="cancelRemark != null">cancel_remark,</if>
            <if test="payExpireTime != null">pay_expire_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orderNo != null and orderNo != ''">#{orderNo},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="storeName != null">#{storeName},</if>
            <if test="userId != null">#{userId},</if>
            <if test="payId != null">#{payId},</if>
            <if test="payAmount != null">#{payAmount},</if>
            <if test="mark != null">#{mark},</if>
            <if test="status != null">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="suitId != null">#{suitId},</if>
            <if test="suitNum != null">#{suitNum},</if>
            <if test="suitAmount != null">#{suitAmount},</if>
            <if test="cancelRemark != null">#{cancelRemark},</if>
            <if test="payExpireTime != null">#{payExpireTime},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="Order" useGeneratedKeys="true" keyProperty="id">
        insert into bst_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            order_no,
            store_id,
            store_name,
            user_id,
            pay_id,
            pay_amount,
            mark,
            status,
            create_time,
            suit_id,
            suit_name,
            suit_num,
            suit_amount,
            cancel_remark,
            pay_expire_time,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.orderNo != null  and i.orderNo != ''">#{i.orderNo},</if>
                <if test="i.orderNo == null  or i.orderNo == ''">default,</if>
                <if test="i.storeId != null ">#{i.storeId},</if>
                <if test="i.storeId == null ">default,</if>
                <if test="i.storeName != null ">#{i.storeName},</if>
                <if test="i.storeName == null ">default,</if>
                <if test="i.userId != null ">#{i.userId},</if>
                <if test="i.userId == null ">default,</if>
                <if test="i.payId != null ">#{i.payId},</if>
                <if test="i.payId == null ">default,</if>
                <if test="i.payAmount != null ">#{i.payAmount},</if>
                <if test="i.payAmount == null ">default,</if>
                <if test="i.mark != null ">#{i.mark},</if>
                <if test="i.mark == null ">default,</if>
                <if test="i.status != null ">#{i.status},</if>
                <if test="i.status == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
                <if test="i.suitId != null ">#{i.suitId},</if>
                <if test="i.suitId == null ">default,</if>
                <if test="i.suitNum != null ">#{i.suitNum},</if>
                <if test="i.suitNum == null ">default,</if>
                <if test="i.suitAmount != null ">#{i.suitAmount},</if>
                <if test="i.suitAmount == null ">default,</if>
                <if test="i.cancelRemark != null ">#{i.cancelRemark},</if>
                <if test="i.cancelRemark == null ">default,</if>
                <if test="i.payExpireTime != null ">#{i.payExpireTime},</if>
                <if test="i.payExpireTime == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_order
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="order_no = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.orderNo != null  and item.orderNo != ''">
                        WHEN #{item.id} THEN #{item.orderNo}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `order_no`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="store_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.storeId != null ">
                        WHEN #{item.id} THEN #{item.storeId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `store_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="store_name = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.storeName != null ">
                        WHEN #{item.id} THEN #{item.storeName}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `store_name`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="user_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userId != null ">
                        WHEN #{item.id} THEN #{item.userId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `user_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="pay_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.payId != null ">
                        WHEN #{item.id} THEN #{item.payId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `pay_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="pay_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.payAmount != null ">
                        WHEN #{item.id} THEN #{item.payAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `pay_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="mark = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.mark != null ">
                        WHEN #{item.id} THEN #{item.mark}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `mark`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="status = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.status != null ">
                        WHEN #{item.id} THEN #{item.status}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="suit_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.suitId != null ">
                        WHEN #{item.id} THEN #{item.suitId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `suit_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="suit_num = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.suitNum != null ">
                        WHEN #{item.id} THEN #{item.suitNum}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `suit_num`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="suit_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.suitAmount != null ">
                        WHEN #{item.id} THEN #{item.suitAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `suit_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="cancel_remark = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.cancelRemark != null ">
                        WHEN #{item.id} THEN #{item.cancelRemark}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `cancel_remark`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="pay_expire_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.payExpireTime != null ">
                        WHEN #{item.id} THEN #{item.payExpireTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `pay_expire_time`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <update id="updateOrder" parameterType="Order">
        update bst_order
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <!-- updateByQuery -->
    <update id="updateByQuery">
        update bst_order bo
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <update id="assembleLastConsumeTime">
        select
        bo.user_id AS `key`,
        MAX(bo.create_time) AS `value`
        from bst_order bo
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </update>

    <sql id="updateColumns">
        <if test="data.orderNo != null and data.orderNo != ''">order_no = #{data.orderNo},</if>
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.storeName != null">store_name = #{data.storeName},</if>
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.payId != null">pay_id = #{data.payId},</if>
        <if test="data.payAmount != null">pay_amount = #{data.payAmount},</if>
        <if test="data.mark != null">mark = #{data.mark},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.suitId != null">suit_id = #{data.suitId},</if>
        <if test="data.suitNum != null">suit_num = #{data.suitNum},</if>
        <if test="data.suitAmount != null">suit_amount = #{data.suitAmount},</if>
        <if test="data.cancelRemark != null">cancel_remark = #{data.cancelRemark},</if>
        <if test="data.payExpireTime != null">pay_expire_time = #{data.payExpireTime},</if>
    </sql>

    <delete id="deleteOrderById" parameterType="Long">
        delete from bst_order where id = #{id}
    </delete>

    <delete id="deleteOrderByIds" parameterType="String">
        delete from bst_order where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
