<?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="cn.daxpay.single.service.core.report.dao.CockpitReportMapper">

    <!-- 查询支付金额 -->
    <select id="getPayAmount" resultType="java.lang.Integer">
        SELECT
            sum(amount)
        FROM
            pay_order
        WHERE
            create_time BETWEEN #{startTime} AND #{endTime}
          AND STATUS IN ( 'success', 'refunding', 'partial_refund', 'refunded' );
    </select>

    <!-- 查询退款金额 -->
    <select id="getRefundAmount" resultType="java.lang.Integer">
        SELECT
            sum(amount)
        FROM
            pay_refund_order
        WHERE
            create_time BETWEEN #{startTime} AND #{endTime}
          AND STATUS = 'success';
    </select>

    <!-- 查询支付订单数量 -->
    <select id="getPayOrderCount" resultType="java.lang.Integer">
        SELECT
            count(*)
        FROM
            pay_order
        WHERE
            create_time BETWEEN #{startTime} AND #{endTime}
          AND STATUS IN ( 'success', 'refunding', 'partial_refund', 'refunded' );
    </select>

    <!-- 查询退款订单数量 -->
    <select id="getRefundOrderCount" resultType="java.lang.Integer">
        SELECT
            count(*)
        FROM
            pay_refund_order
        WHERE
            create_time BETWEEN #{startTime} AND #{endTime}
          AND STATUS = 'success';
    </select>

    <!-- 支付通道折线图 -->
    <select id="getPayChannelInfo" resultType="cn.daxpay.single.service.core.report.entity.ChannelOrderLine">
        SELECT
            channel,
            count(*) as count,
            sum(amount) as sum
        FROM
            pay_order
        WHERE
            create_time BETWEEN #{startTime} AND #{endTime}
          AND STATUS IN ( 'success', 'refunding', 'partial_refund', 'refunded' )
        GROUP BY
            channel
    </select>

    <!-- 退款通道折线图 -->
    <select id="getRefundChannelInfo" resultType="cn.daxpay.single.service.core.report.entity.ChannelOrderLine">
        SELECT
            channel,
            count(*) as count,
            sum(amount) as sum
        FROM
            pay_refund_order
        WHERE
            create_time BETWEEN #{startTime} AND #{endTime}
          AND STATUS = 'success'
        GROUP BY
            channel
    </select>
</mapper>
