<?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.web.modules.order.mapper.BizOrderMapper">

    <resultMap id="BizOrderResult" type="com.ruoyi.web.modules.order.domain.BizOrder">
        <id property="orderId" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="memberId" column="member_id"/>
        <result property="shopId" column="shop_id"/>
        <result property="merchantId" column="merchant_id"/>
        <result property="orderStatus" column="order_status"/>
        <result property="closeType" column="close_type"/>
        <result property="closeReason" column="close_reason"/>
        <result property="payType" column="pay_type"/>
        <result property="payStatus" column="pay_status"/>
        <result property="tradeNo" column="trade_no"/>
        <result property="goodsAmount" column="goods_amount"/>
        <result property="freightAmount" column="freight_amount"/>
        <result property="payAmount" column="pay_amount"/>
        <result property="consigneeName" column="consignee_name"/>
        <result property="consigneeMobile" column="consignee_mobile"/>
        <result property="consigneeAddress" column="consignee_address"/>
        <result property="deliveryType" column="delivery_type"/>
        <result property="logisticsCompany" column="logistics_company"/>
        <result property="trackingNo" column="tracking_no"/>
        <result property="vehicleNo" column="vehicle_no"/>
        <result property="courierName" column="courier_name"/>
        <result property="courierMobile" column="courier_mobile"/>
        <result property="shipRemark" column="ship_remark"/>
        <result property="shipperName" column="shipper_name"/>
        <result property="shipperMobile" column="shipper_mobile"/>
        <result property="shipRegionCode" column="ship_region_code"/>
        <result property="shipRegionName" column="ship_region_name"/>
        <result property="shipDetailAddress" column="ship_detail_address"/>
        <result property="memberNickName" column="member_nick_name"/>
        <result property="shopName" column="shop_name"/>
        <result property="createTime" column="create_time"/>
        <result property="payTime" column="pay_time"/>
        <result property="shipTime" column="ship_time"/>
        <result property="payExpireTime" column="pay_expire_time"/>
        <result property="finishTime" column="finish_time"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <sql id="selectOrderVo">
        select order_id, order_no, member_id, shop_id, merchant_id, order_status, close_type, close_reason,
               pay_type, pay_status, trade_no, goods_amount, freight_amount, pay_amount,
               consignee_name, consignee_mobile, consignee_address, delivery_type,
               logistics_company, tracking_no, vehicle_no, courier_name, courier_mobile, ship_remark,
               shipper_name, shipper_mobile, ship_region_code, ship_region_name, ship_detail_address,
               member_nick_name, shop_name, create_time, pay_time, ship_time, pay_expire_time, finish_time,
               del_flag, create_by, update_by, update_time, remark
        from biz_order
    </sql>

    <sql id="sellerListWhere">
        where o.shop_id = #{shopId} and o.del_flag = '0'
        <if test="query.orderStatus == null or query.orderStatus == ''">
            and o.order_status != '5'
        </if>
        <if test="query.orderStatus != null and query.orderStatus != ''">
            and o.order_status = #{query.orderStatus}
        </if>
        <if test="query.orderNo != null and query.orderNo != ''">
            and o.order_no like concat('%', #{query.orderNo}, '%')
        </if>
        <if test="query.goodsName != null and query.goodsName != ''">
            and exists (
                select 1 from biz_order_item i
                where i.order_id = o.order_id and i.goods_name like concat('%', #{query.goodsName}, '%')
            )
        </if>
        <if test="query.shipStatus != null and query.shipStatus != ''">
            and o.order_status = #{query.shipStatus}
        </if>
        <if test="query.consigneeName != null and query.consigneeName != ''">
            and o.consignee_name like concat('%', #{query.consigneeName}, '%')
        </if>
        <if test="query.consigneeMobile != null and query.consigneeMobile != ''">
            and o.consignee_mobile like concat('%', #{query.consigneeMobile}, '%')
        </if>
        <if test="query.consigneeAddress != null and query.consigneeAddress != ''">
            and o.consignee_address like concat('%', #{query.consigneeAddress}, '%')
        </if>
        <if test="query.deliveryType != null and query.deliveryType != ''">
            and o.delivery_type = #{query.deliveryType}
        </if>
        <if test="query.beginTime != null and query.beginTime != ''">
            and o.create_time &gt;= #{query.beginTime}
        </if>
        <if test="query.endTime != null and query.endTime != ''">
            and o.create_time &lt;= #{query.endTime}
        </if>
    </sql>

    <select id="selectById" resultMap="BizOrderResult">
        <include refid="selectOrderVo"/>
        where order_id = #{orderId} and del_flag = '0'
    </select>

    <select id="selectByIdAndMember" resultMap="BizOrderResult">
        <include refid="selectOrderVo"/>
        where order_id = #{orderId} and member_id = #{memberId} and del_flag = '0'
    </select>

    <select id="selectSellerList" resultMap="BizOrderResult">
        select o.order_id, o.order_no, o.member_id, o.shop_id, o.merchant_id, o.order_status,
               o.pay_amount, o.freight_amount, o.consignee_name, o.consignee_mobile, o.consignee_address,
               o.delivery_type, o.member_nick_name, o.create_time
        from biz_order o
        <include refid="sellerListWhere"/>
        order by o.create_time desc
    </select>

    <select id="selectMemberList" resultMap="BizOrderResult">
        select o.order_id, o.order_no, o.shop_id, o.order_status, o.pay_amount, o.freight_amount,
               o.consignee_name, o.consignee_mobile, o.consignee_address, o.delivery_type,
               o.member_nick_name, o.shop_name, o.create_time, o.pay_status, o.pay_time
        from biz_order o
        where o.member_id = #{memberId} and o.del_flag = '0' and o.order_status != '5'
        <if test="query.orderNo != null and query.orderNo != ''">
            and o.order_no like concat('%', #{query.orderNo}, '%')
        </if>
        <if test="query.goodsName != null and query.goodsName != ''">
            and exists (
                select 1 from biz_order_item i
                where i.order_id = o.order_id and i.goods_name like concat('%', #{query.goodsName}, '%')
            )
        </if>
        <if test="query.payType != null and query.payType != ''">
            and o.pay_type = #{query.payType}
        </if>
        <if test="query.payStatus != null and query.payStatus != ''">
            and o.pay_status = #{query.payStatus}
        </if>
        <if test="query.shipStatus != null and query.shipStatus != ''">
            and o.order_status = #{query.shipStatus}
        </if>
        <if test="query.consigneeName != null and query.consigneeName != ''">
            and o.consignee_name like concat('%', #{query.consigneeName}, '%')
        </if>
        <if test="query.consigneeMobile != null and query.consigneeMobile != ''">
            and o.consignee_mobile like concat('%', #{query.consigneeMobile}, '%')
        </if>
        <if test="query.address != null and query.address != ''">
            and o.consignee_address like concat('%', #{query.address}, '%')
        </if>
        <if test="query.logisticsCompany != null and query.logisticsCompany != ''">
            and o.logistics_company like concat('%', #{query.logisticsCompany}, '%')
        </if>
        <if test="query.beginTime != null and query.beginTime != ''">
            and o.create_time &gt;= #{query.beginTime}
        </if>
        <if test="query.endTime != null and query.endTime != ''">
            and o.create_time &lt;= #{query.endTime}
        </if>
        order by o.create_time desc
    </select>

    <update id="updateForShip" parameterType="com.ruoyi.web.modules.order.domain.BizOrder">
        update biz_order
        set order_status = '2',
            delivery_type = #{deliveryType},
            logistics_company = #{logisticsCompany},
            tracking_no = #{trackingNo},
            vehicle_no = #{vehicleNo},
            courier_name = #{courierName},
            courier_mobile = #{courierMobile},
            ship_remark = #{shipRemark},
            shipper_name = #{shipperName},
            shipper_mobile = #{shipperMobile},
            ship_region_code = #{shipRegionCode},
            ship_region_name = #{shipRegionName},
            ship_detail_address = #{shipDetailAddress},
            ship_time = #{shipTime},
            update_by = #{updateBy},
            update_time = now()
        where order_id = #{orderId}
          and shop_id = #{shopId}
          and order_status = '1'
          and del_flag = '0'
    </update>

    <update id="updateForDelete">
        update biz_order
        set order_status = '5',
            update_by = #{updateBy},
            update_time = now()
        where order_id = #{orderId}
          and shop_id = #{shopId}
          and order_status = '4'
          and del_flag = '0'
    </update>

    <select id="existsUnfinishedByShop" resultType="int">
        select count(1) from biz_order
        where shop_id = #{shopId}
          and order_status in ('0', '1', '2')
          and del_flag = '0'
        limit 1
    </select>

    <select id="existsUnfinishedByMerchant" resultType="int">
        select count(1) from biz_order
        where merchant_id = #{merchantId}
          and order_status in ('0', '1', '2')
          and del_flag = '0'
        limit 1
    </select>

    <insert id="insert" parameterType="com.ruoyi.web.modules.order.domain.BizOrder" useGeneratedKeys="true" keyProperty="orderId">
        insert into biz_order (
            order_no, member_id, shop_id, merchant_id, order_status, pay_status,
            goods_amount, freight_amount, pay_amount,
            consignee_name, consignee_mobile, consignee_address,
            member_nick_name, shop_name, create_time, pay_expire_time, del_flag
        ) values (
            #{orderNo}, #{memberId}, #{shopId}, #{merchantId}, #{orderStatus}, #{payStatus},
            #{goodsAmount}, #{freightAmount}, #{payAmount},
            #{consigneeName}, #{consigneeMobile}, #{consigneeAddress},
            #{memberNickName}, #{shopName}, #{createTime}, #{payExpireTime}, '0'
        )
    </insert>

    <select id="selectMaxOrderNoByPrefix" resultType="String">
        select max(order_no) from biz_order where order_no like concat(#{prefix}, '%')
    </select>

    <update id="updateForPay" parameterType="com.ruoyi.web.modules.order.domain.BizOrder">
        update biz_order
        set order_status = '1',
            pay_status = '1',
            pay_type = #{payType},
            pay_time = #{payTime},
            trade_no = #{tradeNo},
            update_time = now()
        where order_id = #{orderId}
          and member_id = #{memberId}
          and order_status = '0'
          and del_flag = '0'
    </update>

    <update id="updateForCancelPay">
        update biz_order
        set order_status = '4',
            close_type = #{closeType},
            update_time = now()
        where order_id = #{orderId}
          and member_id = #{memberId}
          and order_status = '0'
          and del_flag = '0'
    </update>

    <update id="updateForAftersaleClose">
        update biz_order
        set order_status = '4',
            close_type = #{closeType},
            update_by = #{updateBy},
            update_time = now()
        where order_id = #{orderId}
          and shop_id = #{shopId}
          and order_status in ('1', '2', '3')
          and del_flag = '0'
    </update>

    <select id="selectExpiredPendingPay" resultMap="BizOrderResult">
        <include refid="selectOrderVo"/>
        where order_status = '0'
          and pay_expire_time &lt; #{now}
          and del_flag = '0'
    </select>

    <select id="selectMemberAppList" resultMap="BizOrderResult">
        select o.order_id, o.order_no, o.shop_id, o.order_status, o.close_type, o.pay_status,
               o.goods_amount, o.freight_amount, o.pay_amount, o.shop_name, o.create_time, o.pay_expire_time
        from biz_order o
        where o.member_id = #{memberId} and o.del_flag = '0' and o.order_status != '5'
        <if test="query != null and query.tab != null and query.tab != '' and query.tab != 'ALL'">
            <choose>
                <when test="query.tab == 'PENDING_PAY'">and o.order_status = '0'</when>
                <when test="query.tab == 'PENDING_SHIP'">
                    and o.order_status = '1'
                    and not exists (
                        select 1 from biz_order_aftersale a
                        where a.order_id = o.order_id and a.aftersale_status = '2'
                    )
                </when>
                <when test="query.tab == 'PENDING_RECEIVE'">and o.order_status = '2'</when>
                <when test="query.tab == 'COMPLETED'">and o.order_status = '3'</when>
                <when test="query.tab == 'CLOSED'">and o.order_status = '4'</when>
                <when test="query.tab == 'REVIEW_PENDING'">
                    and o.order_status = '3'
                    and exists (
                        select 1 from biz_order_item oi
                        where oi.order_id = o.order_id
                          and not exists (
                              select 1 from biz_goods_review r
                              where r.order_item_id = oi.item_id and r.del_flag = '0'
                          )
                    )
                </when>
                <when test="query.tab == 'REVIEW_DONE'">
                    and o.order_status = '3'
                    and not exists (
                        select 1 from biz_order_item oi
                        where oi.order_id = o.order_id
                          and not exists (
                              select 1 from biz_goods_review r
                              where r.order_item_id = oi.item_id and r.del_flag = '0'
                          )
                    )
                </when>
            </choose>
        </if>
        order by o.create_time desc
    </select>

    <select id="countMemberByStatus" resultType="int">
        select count(1) from biz_order o
        where o.member_id = #{memberId} and o.order_status = #{orderStatus} and o.del_flag = '0'
        <if test="orderStatus == '1'">
            and not exists (
                select 1 from biz_order_aftersale a
                where a.order_id = o.order_id and a.aftersale_status = '2'
            )
        </if>
    </select>

    <select id="countShopByStatus" resultType="int">
        select count(1) from biz_order
        where shop_id = #{shopId} and order_status = #{orderStatus} and del_flag = '0'
    </select>

    <update id="updateForConfirmReceive">
        update biz_order
        set order_status = '3',
            finish_time = #{finishTime},
            update_time = #{finishTime}
        where order_id = #{orderId}
          and member_id = #{memberId}
          and order_status = '2'
          and del_flag = '0'
    </update>

    <select id="sumPendingSettleByShopId" resultType="java.math.BigDecimal">
        select ifnull(sum(pay_amount), 0)
        from biz_order
        where shop_id = #{shopId}
          and pay_status = '1'
          and order_status in ('1', '2')
          and del_flag = '0'
    </select>

    <select id="selectShippedForAutoConfirm" resultMap="BizOrderResult">
        <include refid="selectOrderVo"/>
        where order_status = '2'
          and ship_time is not null
          and del_flag = '0'
          and datediff(curdate(), date(ship_time)) &gt;= #{autoConfirmDays}
        order by ship_time asc
        limit #{limit}
    </select>

    <select id="sumPlatformPendingSettle" resultType="java.math.BigDecimal">
        select ifnull(sum(pay_amount), 0)
        from biz_order
        where pay_status = '1'
          and order_status in ('1', '2')
          and del_flag = '0'
    </select>
</mapper>
