<?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.mcwl.pay.mapper.OrderTradeMapper">


    <update id="chargeCard">

    </update>

    <select id="getTodayIncome" resultType="java.lang.Double">
        SELECT COALESCE(SUM(payment_amount), 0)
        FROM order_trade
        WHERE create_time >=  CURDATE()
          AND create_time &lt; CURDATE() + INTERVAL 1 DAY
    </select>

    <select id="getMonthIncome" resultType="java.lang.Double">
        SELECT COALESCE(SUM(payment_amount), 0)
        FROM order_trade
        WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01')
        AND create_time &lt; DATE_FORMAT(NOW(), '%Y-%m-01') + INTERVAL 1 MONTH
    </select>

    <select id="getYearIncome" resultType="java.lang.Double">
        SELECT COALESCE(SUM(payment_amount), 0)
        FROM order_trade
        WHERE create_time >= CONCAT(YEAR(NOW()), '-01-01')
          AND create_time &lt; CONCAT(YEAR(NOW()) + 1, '-01-01')
    </select>

    <select id="getIncomeByYear" resultType="java.lang.Double">
        SELECT COALESCE(SUM(payment_amount), 0)
        FROM order_trade
        WHERE create_time >= CONCAT(#{year}, '-01-01')
          AND create_time &lt; CONCAT(#{year} + 1, '-01-01')
    </select>

    <select id="getTotalIncome" resultType="java.lang.Double">
        SELECT COALESCE(SUM(payment_amount), 0)
        FROM order_trade
    </select>

    <select id="getIncomeByMonth" resultType="java.lang.Double">
        SELECT COALESCE(SUM(payment_amount), 0)
        FROM order_trade
        WHERE
        create_time >= CONCAT(#{year}, '-', LPAD(#{month}, 2, '0'), '-01')
        AND create_time &lt; CONCAT(#{year}, '-', LPAD(#{month}, 2, '0'), '-01') + INTERVAL 1 MONTH
    </select>


</mapper>
