<?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.ga.core.mapper.settle.OverrideSettleMapper">

    <resultMap id="VOMap" type="com.ga.core.vo.settle.OverrideSettleVO"/>

    <insert id="insert" parameterType="com.ga.core.vo.settle.OverrideSettleVO" useGeneratedKeys="true" keyProperty="osId">
        INSERT INTO override_settle (settle_id, upper_agent_id, lower_agent_id, settle_month,
                                     base_amount, override_pct, override_amount, calc_date)
        VALUES (#{settleId}, #{upperAgentId}, #{lowerAgentId}, #{settleMonth},
                #{baseAmount}, #{overridePct}, #{overrideAmount}, NOW())
    </insert>

    <insert id="insertBatch">
        INSERT INTO override_settle (settle_id, upper_agent_id, lower_agent_id, settle_month,
                                     base_amount, override_pct, override_amount, calc_date)
        VALUES
        <foreach collection="list" item="r" separator=",">
            (#{r.settleId}, #{r.upperAgentId}, #{r.lowerAgentId}, #{r.settleMonth},
             #{r.baseAmount}, #{r.overridePct}, #{r.overrideAmount}, NOW())
        </foreach>
    </insert>

    <select id="selectByUpper" resultMap="VOMap">
        SELECT * FROM override_settle
        WHERE upper_agent_id = #{upperAgentId} AND settle_month = #{settleMonth}
        ORDER BY lower_agent_id
    </select>

    <delete id="deleteBySettleMonth">
        DELETE FROM override_settle WHERE settle_month = #{settleMonth}
    </delete>

    <select id="aggregateByUpper" resultType="map">
        SELECT upper_agent_id AS "agentId", SUM(override_amount) AS "total"
        FROM override_settle WHERE settle_month = #{settleMonth}
        GROUP BY upper_agent_id
    </select>

    <select id="sumByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(override_amount), 0) FROM override_settle WHERE settle_month = #{settleMonth}
    </select>

</mapper>
