<?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="org.jeecg.modules.shop.commission.mapper.PartnerCommissionMapper">

    <select id="pagList" resultType="org.jeecg.modules.shop.commission.entity.vo.PartnerCommissionVO">
select a.*,b.name as customerName
    from partner_commission as a
    left join partner_customer as b on a.customer_id = b.id
    </select>
    <select id="moneyCount" resultType="org.jeecg.modules.shop.commission.entity.vo.CommissionCountVO">
        SELECT
            user_id AS userId,
            SUM(money) AS totalCommission, -- 总佣金
            SUM(CASE WHEN state = 1 THEN money ELSE 0 END) AS paidCommission, -- 已发放
            SUM(CASE WHEN state = 0 THEN money ELSE 0 END) AS unpaidCommission -- 未发放
        FROM partner_commission
        where user_id = #{userId}
    </select>
    <select id="getCommissionByUserAndYear" resultType="java.math.BigDecimal">
        SELECT
            SUM(money) AS totalCommission
        FROM partner_customer as a
            inner join partner_commission as b on a.id = b.customer_id
        where a.user_id = #{userId}
        and YEAR(a.create_time) = #{year}
        and b.type = 0
        and b.state = 1
    </select>
    <select id="getOrderCommissionByUserAndYear" resultType="java.math.BigDecimal">
        SELECT
            SUM(money) AS totalCommission
        FROM partner_customer as a
                 inner join partner_commission as b on a.id = b.customer_id
        where a.user_id = #{userId}
          and YEAR(a.create_time) = #{year}
          and b.type = 1
          and b.state = 1
    </select>
</mapper>