<?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.xxfc.platform.order.mapper.DailyVehicleOrderStatisticsMapper">
    <!--    获取每日租车订单成交金和押金总额-->
    <select id="getGmvAndMarginTotal" resultType="com.xxfc.platform.order.entity.DailyVehicleOrderStatistics">
        SELECT
        v.start_company_id AS branchCompanyId,
        date(DATE_SUB(now(),interval #{day} day)) as oneDay,
        IFNULL(SUM(b.real_amount-v.deposit),0) AS gmv,
        IFNULL(SUM(v.deposit),0) AS securityDeposit
        FROM
        base_order b
        LEFT JOIN
        order_rent_vehicle_detail v
        ON
        b.id=v.order_id
        WHERE
        b.type=1
        AND
        DATE(FROM_UNIXTIME(b.pay_time/1000))=DATE(DATE_SUB(now(),INTERVAL #{day} DAY))
        GROUP  BY
        v.start_company_id
    </select>


    <!--    退还押金总额-->
    <select id="getRefundSecurityDeposit" resultType="com.xxfc.platform.order.entity.DailyVehicleOrderStatistics">
        SELECT
        v.start_company_id AS branchCompanyId,
        IFNULL(SUM(r.refund_amount),0) AS refundSecurityDeposit
        FROM
        base_order o
        LEFT JOIN
        order_refund r
        ON
        o.id=r.order_id
        LEFT JOIN
        order_rent_vehicle_detail v
        ON
        v.order_id=o.id
        WHERE
        o.type=1
        AND
        r.refund_status=1
        AND
        r.refund_type >1
        AND
        DATE(FROM_UNIXTIME(r.refund_time/1000))=DATE(DATE_SUB(now(),INTERVAL #{day} DAY))
        GROUP BY
        v.start_company_id
    </select>


    <!--    取消订单扣除款项总和-->
    <select id="getPenalSum" resultType="com.xxfc.platform.order.entity.DailyVehicleOrderStatistics">
        SELECT
        v.start_company_id as branchCompanyId,
        IFNULL(SUM(r.deduct_amount),0) as penalSum
        FROM
        order_refund r
        LEFT JOIN
        base_order o
        ON
        o.id=r.order_id
        LEFT JOIN
        order_rent_vehicle_detail v
        ON
        v.order_id=o.id
        WHERE
        o.type=1
        AND
        r.refund_status=1
        AND
        r.refund_type =1
        AND
        date(FROM_UNIXTIME(r.refund_time/1000))=date(DATE_SUB(now(),interval #{day} day))
        GROUP BY
        v.start_company_id
    </select>

    <!--    -->
    <select id="monthOrderTotal" resultType="com.xxfc.platform.order.entity.OrderStatistics">
        SELECT
        IFNULL( sum( gmv ), 0 ) AS totalGmv,
        IFNULL( sum( security_deposit ), 0 ) AS totalSecurityDeposit,
        IFNULL( sum( refund_security_deposit ), 0 ) AS totalRefundSecurityDeposit,
        IFNULL( sum( forfeit ), 0 ) AS totalForfeit,
        IFNULL( sum( compensation ), 0 ) AS totalCompensation,
        IFNULL( sum( return_gmv ), 0 ) AS totalReturnGmv,
        IFNULL( sum( default_money ), 0 ) AS totalDefaultMoney,
        IFNULL( sum( total_expenditure ), 0 ) AS totalExpenditure
        FROM
        daily_vehicle_order_statistics
        WHERE
        1=1
        <if test="companyId != null">
           and branch_company_id = #{companyId}
        </if>
    </select>

    <!--违章-->
    <select id="getCompensationAndPostpone" resultType="HashMap">
        SELECT
        v.start_company_id AS branchCompanyId,
        c.ded_detail AS dedDetail
        FROM
        order_refund r
        LEFT JOIN
        order_vehicle_crosstown c
        on
        r.order_id = c.order_id
        LEFT JOIN
        order_rent_vehicle_detail v
        ON
        r.order_id=v.order_id
        WHERE
        r.refund_status=1
        and
        r.refund_type = 3
        and
        c.type=3
        and
        date(FROM_UNIXTIME(refund_time/1000))=date(DATE_SUB(now(),interval #{day} day))
        order by v.start_company_id asc
    </select>


    <select id="getViolationMoney" resultType="com.xxfc.platform.order.entity.DailyVehicleOrderStatistics">
        SELECT
		d.start_company_id AS branchCompanyId,
        IFNULL(sum(v.price),0) as violationMoney
        FROM
        order_refund r
				LEFT JOIN
        base_order o
        on
        r.order_id= o.id
        LEFT JOIN
        order_rent_vehicle_detail d
        on
        r.order_id= d.order_id
        left join
        order_violation v
        on
        d.id = v.detail_id
        WHERE
				o.type=1
				and
        r.refund_status=1
        and
        r.refund_type = 4
        and
        date(FROM_UNIXTIME(r.refund_time/1000))=date(DATE_SUB(now(),interval #{day} day))
				GROUP BY
				d.start_company_id
    </select>

<!--    <select id="updateByExampleSelective">-->
<!--                        SELECT-->
<!--        IFNULL(sum(gmv),0) as totalGmv,-->
<!--        (IFNULL(sum(security_deposit),0)-IFNULL(sum(refund_security_deposit) ,0)) as totalSecurityDeposit,-->
<!--        IFNULL(sum(refund_security_deposit) ,0) as totalRefundSecurityDeposit,-->
<!--        IFNULL(sum(compensation) ,0) as totalCompensation,-->
<!--        IFNULL(sum(forfeit) ,0) as totalForfeit,-->
<!--        IFNULL(sum(penal_sum) ,0) as totalPenalSum,-->
<!--        IFNULL(sum(postpone) ,0) as totalPostpone-->
<!--        FROM-->
<!--        daily_vehicle_order_statistics-->
<!--        WHERE-->
<!--        branch_company_id =#{companyId}-->
<!--        and-->
<!--        DATE_FORMAT(one_day,'%Y-%c')=DATE_FORMAT(DATE_SUB(NOW(),interval 1 day),'%Y-%c')-->
<!--    </select>-->
</mapper>