<?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 column="suit_start_rule" property="suitStartRule" typeHandler="com.ruoyi.bst.suit.mapper.typehanlder.SuitStartFeeRuleJsonTypeHandler"/>
        <result column="suit_interval_rule" property="suitIntervalRule" typeHandler="com.ruoyi.bst.suit.mapper.typehanlder.SuitIntervalFeeRuleJsonListTypeHandler"/>
    </resultMap>

    <sql id="selectOrderVo">
        select
            bo.id,
            bo.no,
            bo.suit_type,
            bo.area_id,
            bo.user_id,
            bo.pay_id,
            bo.total_fee,
            bo.deposit_fee,
            bo.dispatch_fee,
            bo.manage_fee,
            bo.riding_fee,
            bo.deduction_fee,
            bo.mark,
            bo.duration,
            bo.distance,
            bo.status,
            bo.create_time,
            bo.start_time,
            bo.end_time,
            bo.return_type,
            bo.audio_files,
            bo.suit_id,
            bo.suit_name,
            bo.suit_free_ride_time,
            bo.suit_rental_unit,
            bo.suit_riding_rule,
            bo.suit_start_rule,
            bo.return_mode,
            bo.suit_interval_rule,
            bo.return_lon,
            bo.return_lat,
            bo.pay_amount,
            bo.pay_expire_time,
            bo.max_time,
            bo.suit_seconds,
            bo.start_area_sub_id,
            bo.end_area_sub_id,
            bo.start_area_sub_name,
            bo.end_area_sub_name,
            bo.end_reason,
            bo.order_device_id,
            bo.area_return_verify,
            bo.verify_remark,
            bo.area_user_id,
            bo.area_agent_id,
            ba.name as area_name,
            su.nick_name as user_name,
            su.phonenumber as user_phone,
            bp.no as pay_no,
            bp.refunded as pay_refunded,
            bp.refunding as pay_refunding,
            bp.pay_time as pay_time,
            bp.channel_name as pay_channel_name,
            bod.device_id,
            bod.device_mac,
            bod.device_sn,
            bod.device_vehicle_num,
            bod.device_mch_id
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_order bo
            left join bst_area ba on bo.area_id = ba.id
            left join sys_user su on bo.user_id = su.user_id
            left join bst_pay bp on bo.pay_id = bp.id
            left join bst_order_device bod on bod.id = bo.order_device_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bo.id = #{query.id}</if>
        <if test="query.no != null  and query.no != ''"> and bo.no like concat('%', #{query.no}, '%')</if>
        <if test="query.suitType != null  and query.suitType != ''"> and bo.suit_type = #{query.suitType}</if>
        <if test="query.areaId != null "> and bo.area_id = #{query.areaId}</if>
        <if test="query.userId != null "> and bo.user_id = #{query.userId}</if>
        <if test="query.deviceId != null "> and bo.device_id = #{query.deviceId}</if>
        <if test="query.deviceMac != null  and query.deviceMac != ''"> and bo.device_mac like concat('%', #{query.deviceMac}, '%')</if>
        <if test="query.deviceSn != null  and query.deviceSn != ''"> and bo.device_sn like concat('%', #{query.deviceSn}, '%')</if>
        <if test="query.payId != null "> and bo.pay_id = #{query.payId}</if>
        <if test="query.mark != null  and query.mark != ''"> and bo.mark like concat('%', #{query.mark}, '%')</if>
        <if test="query.status != null  and query.status != ''"> and bo.status = #{query.status}</if>
        <if test="query.returnType != null  and query.returnType != ''"> and bo.return_type = #{query.returnType}</if>
        <if test="query.suitId != null "> and bo.suit_id = #{query.suitId}</if>
        <if test="query.suitRentalUnit != null  and query.suitRentalUnit != ''"> and bo.suit_rental_unit = #{query.suitRentalUnit}</if>
        <if test="query.suitRidingRule != null  and query.suitRidingRule != ''"> and bo.suit_riding_rule = #{query.suitRidingRule}</if>
        <if test="query.returnMode != null  and query.returnMode != ''"> and bo.return_mode = #{query.returnMode}</if>
        <if test="query.suitName != null  and query.suitName != ''"> and bo.suit_name like concat('%', #{query.suitName}, '%')</if>
        <if test="query.areaName != null  and query.areaName != ''"> and ba.name like concat('%', #{query.areaName}, '%')</if>
        <if test="query.userName != null  and query.userName != ''"> and su.nick_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.deviceVehicleNum != null  and query.deviceVehicleNum != ''"> and bo.device_vehicle_num like concat('%', #{query.deviceVehicleNum}, '%')</if>
        <if test="query.payNo != null  and query.payNo != ''"> and bp.no like concat('%', #{query.payNo}, '%')</if>
        <if test="query.userPhone != null  and query.userPhone != ''"> and su.phonenumber like concat('%', #{query.userPhone}, '%')</if>
        <if test="query.eqNo != null  and query.eqNo != ''"> and bo.no = #{query.eqNo}</if>
        <if test="query.startAreaSubId != null "> and bo.start_area_sub_id = #{query.startAreaSubId}</if>
        <if test="query.endAreaSubId != null "> and bo.end_area_sub_id = #{query.endAreaSubId}</if>
        <if test="query.startAreaSubName != null  and query.startAreaSubName != ''"> and bo.start_area_sub_name like concat('%', #{query.startAreaSubName}, '%')</if>
        <if test="query.endAreaSubName != null  and query.endAreaSubName != ''"> and bo.end_area_sub_name like concat('%', #{query.endAreaSubName}, '%')</if>
        <if test="query.maxTimeStart != null"> and bo.max_time &gt;= #{query.maxTimeStart}</if>
        <if test="query.maxTimeEnd != null"> and bo.max_time &lt;= #{query.maxTimeEnd}</if>
        <if test="query.deviceMchId != null "> and bo.device_mch_id = #{query.deviceMchId}</if>
        <if test="query.endReason != null  and query.endReason != ''"> and bo.end_reason like concat('%', #{query.endReason}, '%')</if>
        <if test="query.orderDeviceId != null "> and bo.order_device_id = #{query.orderDeviceId}</if>
        <if test="query.areaReturnVerify != null "> and bo.area_return_verify = #{query.areaReturnVerify}</if>
        <if test="query.verifyRemark != null  and query.verifyRemark != ''"> and bo.verify_remark like concat('%', #{query.verifyRemark}, '%')</if>
        <if test="query.areaUserId != null "> and bo.area_user_id = #{query.areaUserId}</if>
        <if test="query.areaAgentId != null "> and bo.area_agent_id = #{query.areaAgentId}</if>
        <if test="query.createDate != null "> and date(bo.create_time) = #{query.createDate}</if>
        <if test="query.bonusUserId != null ">
            and bo.id in (
                select distinct bb.bst_id from bst_bonus bb
                where bb.arrival_id = #{query.bonusUserId} and bb.arrival_type in ('AGENT', 'MCH', 'JOIN', 'PARTNER')
                and bb.bst_type = '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>
        <if test="query.statusList != null and query.statusList.size() > 0"> and bo.status in
            <foreach item="item" collection="query.statusList" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        ${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>
    </select>

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

    <insert id="insertOrder" parameterType="Order" useGeneratedKeys="true" keyProperty="id">
        insert into bst_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="no != null and no != ''">no,</if>
            <if test="suitType != null and suitType != ''">suit_type,</if>
            <if test="areaId != null">area_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="payId != null">pay_id,</if>
            <if test="totalFee != null">total_fee,</if>
            <if test="depositFee != null">deposit_fee,</if>
            <if test="dispatchFee != null">dispatch_fee,</if>
            <if test="manageFee != null">manage_fee,</if>
            <if test="ridingFee != null">riding_fee,</if>
            <if test="deductionFee != null">deduction_fee,</if>
            <if test="mark != null">mark,</if>
            <if test="duration != null">duration,</if>
            <if test="distance != null">distance,</if>
            <if test="status != null">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="returnType != null">return_type,</if>
            <if test="audioFiles != null">audio_files,</if>
            <if test="suitId != null">suit_id,</if>
            <if test="suitName != null">suit_name,</if>
            <if test="suitFreeRideTime != null">suit_free_ride_time,</if>
            <if test="suitRentalUnit != null">suit_rental_unit,</if>
            <if test="suitRidingRule != null">suit_riding_rule,</if>
            <if test="suitStartRule != null">suit_start_rule,</if>
            <if test="returnMode != null">return_mode,</if>
            <if test="suitIntervalRule != null">suit_interval_rule,</if>
            <if test="returnLon != null">return_lon,</if>
            <if test="returnLat != null">return_lat,</if>
            <if test="payAmount != null">pay_amount,</if>
            <if test="payExpireTime != null">pay_expire_time,</if>
            <if test="maxTime != null">max_time,</if>
            <if test="suitSeconds != null">suit_seconds,</if>
            <if test="startAreaSubId != null">start_area_sub_id,</if>
            <if test="endAreaSubId != null">end_area_sub_id,</if>
            <if test="startAreaSubName != null">start_area_sub_name,</if>
            <if test="endAreaSubName != null">end_area_sub_name,</if>
            <if test="endReason != null">end_reason,</if>
            <if test="orderDeviceId != null">order_device_id,</if>
            <if test="areaReturnVerify != null">area_return_verify,</if>
            <if test="verifyRemark != null">verify_remark,</if>
            <if test="areaUserId != null">area_user_id,</if>
            <if test="areaAgentId != null">area_agent_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="no != null and no != ''">#{no},</if>
            <if test="suitType != null and suitType != ''">#{suitType},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="payId != null">#{payId},</if>
            <if test="totalFee != null">#{totalFee},</if>
            <if test="depositFee != null">#{depositFee},</if>
            <if test="dispatchFee != null">#{dispatchFee},</if>
            <if test="manageFee != null">#{manageFee},</if>
            <if test="ridingFee != null">#{ridingFee},</if>
            <if test="deductionFee != null">#{deductionFee},</if>
            <if test="mark != null">#{mark},</if>
            <if test="duration != null">#{duration},</if>
            <if test="distance != null">#{distance},</if>
            <if test="status != null">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="returnType != null">#{returnType},</if>
            <if test="audioFiles != null">#{audioFiles},</if>
            <if test="suitId != null">#{suitId},</if>
            <if test="suitName != null">#{suitName},</if>
            <if test="suitFreeRideTime != null">#{suitFreeRideTime},</if>
            <if test="suitRentalUnit != null">#{suitRentalUnit},</if>
            <if test="suitRidingRule != null">#{suitRidingRule},</if>
            <if test="suitStartRule != null">#{suitStartRule, typeHandler=com.ruoyi.bst.suit.mapper.typehanlder.SuitStartFeeRuleJsonTypeHandler},</if>
            <if test="returnMode != null">#{returnMode},</if>
            <if test="suitIntervalRule != null">#{suitIntervalRule, typeHandler=com.ruoyi.bst.suit.mapper.typehanlder.SuitIntervalFeeRuleJsonListTypeHandler},</if>
            <if test="returnLon != null">#{returnLon},</if>
            <if test="returnLat != null">#{returnLat},</if>
            <if test="payAmount != null">#{payAmount},</if>
            <if test="payExpireTime != null">#{payExpireTime},</if>
            <if test="maxTime != null">#{maxTime},</if>
            <if test="suitSeconds != null">#{suitSeconds},</if>
            <if test="startAreaSubId != null">#{startAreaSubId},</if>
            <if test="endAreaSubId != null">#{endAreaSubId},</if>
            <if test="startAreaSubName != null">#{startAreaSubName},</if>
            <if test="endAreaSubName != null">#{endAreaSubName},</if>
            <if test="endReason != null">#{endReason},</if>
            <if test="orderDeviceId != null">#{orderDeviceId},</if>
            <if test="areaReturnVerify != null">#{areaReturnVerify},</if>
            <if test="verifyRemark != null">#{verifyRemark},</if>
            <if test="areaUserId != null">#{areaUserId},</if>
            <if test="areaAgentId != null">#{areaAgentId},</if>
         </trim>
    </insert>

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

    <sql id="updateColumns">
        <if test="data.no != null and data.no != ''">no = #{data.no},</if>
        <if test="data.suitType != null and data.suitType != ''">suit_type = #{data.suitType},</if>
        <if test="data.areaId != null">area_id = #{data.areaId},</if>
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.payId != null">pay_id = #{data.payId},</if>
        <if test="data.totalFee != null">total_fee = #{data.totalFee},</if>
        <if test="data.depositFee != null">deposit_fee = #{data.depositFee},</if>
        <if test="data.dispatchFee != null">dispatch_fee = #{data.dispatchFee},</if>
        <if test="data.manageFee != null">manage_fee = #{data.manageFee},</if>
        <if test="data.ridingFee != null">riding_fee = #{data.ridingFee},</if>
        <if test="data.deductionFee != null">deduction_fee = #{data.deductionFee},</if>
        <if test="data.mark != null">mark = #{data.mark},</if>
        <if test="data.duration != null">duration = #{data.duration},</if>
        <if test="data.distance != null">distance = #{data.distance},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.startTime != null">start_time = #{data.startTime},</if>
        <if test="data.endTime != null">end_time = #{data.endTime},</if>
        <if test="data.returnType != null">return_type = #{data.returnType},</if>
        <if test="data.audioFiles != null">audio_files = #{data.audioFiles},</if>
        <if test="data.suitId != null">suit_id = #{data.suitId},</if>
        <if test="data.suitName != null">suit_name = #{data.suitName},</if>
        <if test="data.suitFreeRideTime != null">suit_free_ride_time = #{data.suitFreeRideTime},</if>
        <if test="data.suitRentalUnit != null">suit_rental_unit = #{data.suitRentalUnit},</if>
        <if test="data.suitRidingRule != null">suit_riding_rule = #{data.suitRidingRule},</if>
        <if test="data.suitStartRule != null">suit_start_rule = #{data.suitStartRule, typeHandler=com.ruoyi.bst.suit.mapper.typehanlder.SuitStartFeeRuleJsonTypeHandler},</if>
        <if test="data.returnMode != null">return_mode = #{data.returnMode},</if>
        <if test="data.suitIntervalRule != null">suit_interval_rule = #{data.suitIntervalRule, typeHandler=com.ruoyi.bst.suit.mapper.typehanlder.SuitIntervalFeeRuleJsonListTypeHandler},</if>
        <if test="data.returnLon != null">return_lon = #{data.returnLon},</if>
        <if test="data.returnLat != null">return_lat = #{data.returnLat},</if>
        <if test="data.payAmount != null">pay_amount = #{data.payAmount},</if>
        <if test="data.payExpireTime != null">pay_expire_time = #{data.payExpireTime},</if>
        <if test="data.maxTime != null">max_time = #{data.maxTime},</if>
        <if test="data.suitSeconds != null">suit_seconds = #{data.suitSeconds},</if>
        <if test="data.startAreaSubId != null">start_area_sub_id = #{data.startAreaSubId},</if>
        <if test="data.endAreaSubId != null">end_area_sub_id = #{data.endAreaSubId},</if>
        <if test="data.startAreaSubName != null">start_area_sub_name = #{data.startAreaSubName},</if>
        <if test="data.endAreaSubName != null">end_area_sub_name = #{data.endAreaSubName},</if>
        <if test="data.endReason != null">end_reason = #{data.endReason},</if>
        <if test="data.orderDeviceId != null">order_device_id = #{data.orderDeviceId},</if>
        <if test="data.areaReturnVerify != null">area_return_verify = #{data.areaReturnVerify},</if>
        <if test="data.verifyRemark != null">verify_remark = #{data.verifyRemark},</if>
        <if test="data.areaUserId != null">area_user_id = #{data.areaUserId},</if>
        <if test="data.areaAgentId != null">area_agent_id = #{data.areaAgentId},</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>

    <!-- updateByQuery -->

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

    <!-- selectUserCount -->

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


    <!-- selectCount -->

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

    <!-- selectSumOfPayAmount -->

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

    <!-- selectSumOfRefundedAmount -->

    <select id="selectSumOfRefundAmount" resultType="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>

    <!-- selectDailyStat -->

    <select id="selectDailyStat" resultType="OrderDailyStatVO">
        select
            <foreach item="item" collection="keys" separator=",">
                ${item}
            </foreach>
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `date`
    </select>

    <!-- selectDailyRefundStat -->

    <select id="selectDailyRefundStat" resultType="OrderDailyRefundStatVO">
        select
            <foreach item="item" collection="keys" separator=",">
                ${item}
            </foreach>
        from <include refid="searchTables"/>
            left join bst_refund br on br.pay_id = bp.id
        <where>
            <include refid="searchConditionForRefund"/>
        </where>
        group by `date`
    </select>

    <!-- selectRank --> 

    <resultMap id="OrderRankVO" type="OrderRankVO" autoMapping="true">
        <result property="actualAmount" column="actual_amount" typeHandler="com.ruoyi.common.mybatis.typehandler.NonNullDecimalTypeHandler"/>
        <result property="count" column="count" typeHandler="com.ruoyi.common.mybatis.typehandler.NonNullIntegerTyperHandler"/>
        <result property="processingCount" column="processing_count" typeHandler="com.ruoyi.common.mybatis.typehandler.NonNullIntegerTyperHandler"/>
    </resultMap>

    <select id="selectRank" resultMap="OrderRankVO">
        select 
            bo.area_id,
            ba.name as area_name,
            sum(bp.refunded) - sum(bo.pay_amount) as actual_amount,
            count(bo.id) as `count`,
            sum(if(bo.status = 'PROCESSING', 1, 0)) as processing_count
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by bo.area_id,ba.name
    </select>
</mapper>
