<?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.dayou.mapper.BrokerageVariableMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dayou.entity.BrokerageVariable">
        <result column="id" property="id" />
        <result column="deleted" property="deleted" />
        <result column="created" property="created" />
        <result column="modified" property="modified" />
        <result column="payment_id" property="paymentId" />
        <result column="user_id" property="userId" />
        <result column="user_type" property="userType" />
        <result column="brokerage_amount" property="brokerageAmount" />
        <result column="settled_amount" property="settledAmount" />
        <result column="unsettled_amount" property="unsettledAmount" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        deleted,
        created,
        modified,
        payment_id, user_id, user_type, brokerage_amount, settled_amount, unsettled_amount
    </sql>

    <select id="getCompensationBrokerage" parameterType="java.lang.Long" resultType="com.dayou.entity.BrokerageVariable">
        SELECT
            bv.id,
            bv.payment_id,
            bv.unsettled_amount,
            bv.brokerage_amount,
            bv.user_id,
            ibd.id as brokerageDetailId
        FROM
            brokerage_variable bv
                left join payment_collection pc on bv.payment_id = pc.id
                left join item_brokerage_general ibg on ibg.item_id = pc.item_id
                left join item_brokerage_detail ibd on ibg.id = ibd.general_id and ibd.user_id = bv.user_id
        WHERE
        bv.user_id = #{userId} and bv.unsettled_amount != 0 and ibd.general_id = #{generalId} and bv.deleted = 0 and ibd.deleted = 0
        and pc.payment_date &gt;= concat( YEAR ( now())- 1, '-12-23' )
        AND pc.payment_date &lt;=   concat( YEAR ( now()), '-12-22' )
    </select>
</mapper>
