<?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.DailyTravelOrderStatisticsMapper">
    <!--    获取旅游订单成交金-->
    <select id="getTravelGmv" resultType="com.xxfc.platform.order.entity.DailyTravelOrderStatistics">
        select
        t.start_company_id as branchCompanyId,
        date(DATE_SUB(now(),interval #{day} day)) as oneDay,
        IFNULL(SUM(o.real_amount),0) as gmv
        from
        base_order o
        left join
        order_tour_detail t
        on
        o.id=t.order_id
        where
        type=2
        and
        date(FROM_UNIXTIME(o.pay_time/1000))=date(DATE_SUB(now(),interval #{day} day))
        group by
        t.start_company_id
    </select>

    <!--    取消订单扣除款项总和-->
    <select id="getTravelPenalSum" resultType="com.xxfc.platform.order.entity.DailyTravelOrderStatistics">
        SELECT
        t.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_tour_detail t
        on
        o.id=t.order_id
        WHERE
        o.type=2
        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
        t.start_company_id
    </select>

    <select id="monthOrderTotal" resultType="com.xxfc.platform.order.entity.OrderStatistics">
        SELECT
        IFNULL(sum(gmv),0) as totalGmv,
        IFNULL(sum(penal_sum) ,0) as totalPenalSum
        FROM
        (
        SELECT
        ANY_VALUE(id) AS id,
        ANY_VALUE(branch_company_id) AS branch_company_id,
        ANY_VALUE(one_day) AS one_day,
        ANY_VALUE(gmv) AS gmv,
        ANY_VALUE(penal_sum) AS penal_sum,
        ANY_VALUE(crt_time) AS crt_time
        FROM
        daily_travel_order_statistics
        GROUP BY
        one_day,branch_company_id
        ) o
        WHERE
        1=1
        <if test="companyId != null">
            and branch_company_id =#{companyId}
        </if>
    </select>

    <!--    <select id="updateByExampleSelective">-->
    <!--        SELECT-->
    <!--        IFNULL(sum(gmv),0) as totalGmv,-->
    <!--        IFNULL(sum(penal_sum) ,0) as totalPenalSum-->
    <!--        FROM-->
    <!--        daily_travel_order_statistics-->
    <!--        WHERE-->
    <!--        branch_company_id =#{branchCompanyId}-->
    <!--        AND-->
    <!--        DATE_FORMAT(one_day,'%Y-%c')=DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%c')-->
    <!--    </select>-->
</mapper>