<?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.pluss.platform.mapper.UserProfitMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.pluss.platform.entity.UserProfit">
        <id column="id" property="id" />
        <result column="userId" property="userId" />
        <result column="parentId" property="parentId" />
        <result column="roleCode" property="roleCode" />
        <result column="teamTransFlow" property="teamTransFlow" />
        <result column="ownTransFlow" property="ownTransFlow" />
        <result column="directTransFlow" property="directTransFlow" />
        <result column="totalProfitAmt" property="totalProfitAmt" />
        <result column="directProfitAmt" property="directProfitAmt" />
        <result column="indirectProfitAmt" property="indirectProfitAmt" />
        <result column="otherProfitAmt" property="otherProfitAmt" />
        <result column="t2ProfitAmt" property="t2ProfitAmt" />
        <result column="t3ProfitAmt" property="t3ProfitAmt" />
        <result column="dividProfitAmt" property="dividProfitAmt" />
        <result column="areaFlow" property="areaFlow" />
        <result column="areaProfitAmt" property="areaProfitAmt" />
        <result column="createTime" property="createTime" />
        <result column="profitDate" property="profitDate" />
        <result column="remarks" property="remarks" />
        <result column="transNum" property="transNum" />
    </resultMap>

    <select id="selectLevelFlow" resultType="cn.pluss.platform.entity.UserProfit">
        SELECT SUM(teamTransFlow) as teamTransFlow,userId FROM tb_pluss_user_profit
        WHERE profitMonth = #{date} AND teamTransFlow != 0 and roleCode != 'ZY_BST' AND levelCode not in ('LV9','LV10') GROUP BY userId
    </select>

    <select id="selectPageSubTeamData" resultType="cn.pluss.platform.vo.UserAppVO">
        SELECT
            IFNULL(ua.userName, SUBSTR(ui.phone, 1, 11)) `userName`, ua.userId, ua.logo,
               SUBSTR(ui.phone, 1, 11) phone, ua.levelCode, IFNULL(t2.lastMonthTranFee, 0.00) lastMonthTransFee,
            IFNULL(t2.monthTransFee, 0.00) monthTransFee, ua.createDt, ua.createDt createTime, ua.inviteNum,
            IF(t2.monthTransFee = 0.00 || t2.monthTransFee IS NULL, 0, IF(t2.lastMonthTranFee = 0.00 || t2.lastMonthTranFee IS NULL, 1, ROUND((t2.monthTransFee / t2.lastMonthTranFee), 2))) rate,
            IF((t2.monthTransFee / t2.lastMonthTranFee) &lt; 1, 2, 1) rateFlag
        FROM tb_pluss_user_app ua LEFT JOIN
            tb_pluss_user_info ui ON ui.id = ua.userId
        LEFT JOIN (
            SELECT
                t.userId,
                SUM(IF(t.profitMonth = DATE_FORMAT(CURRENT_DATE, '%Y-%m'), t.monthTransFee, 0)) monthTransFee,
                SUM(IF(t.profitMonth = DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m'), t.monthTransFee, 0)) lastMonthTranFee
            FROM (
                SELECT SUM(up.ownTransFlow + up.teamTransFlow) monthTransFee, profitMonth, up.userId
                FROM tb_pluss_user_profit up
                WHERE parentId = #{userId} AND profitMonth IN (DATE_FORMAT(CURRENT_DATE, '%Y-%m'), DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m'))
                GROUP BY profitMonth, up.userId
            ) t GROUP BY t.userId
        ) t2 ON ua.userId = t2.userId WHERE ua.parentId = #{userId} AND ua.userType IN ('agent', 'promoter') AND ui.loginName NOT LIKE '%@%'
        <if test="userName != null and userName != ''">
            AND ua.userName LIKE CONCAT('%', #{userName}, '%')
        </if>
        ORDER BY ua.createDt DESC
    </select>


    <select id="pageData" resultType="cn.pluss.platform.entity.UserProfit">
        SELECT up.*,ua.userName from tb_pluss_user_profit up left join tb_pluss_user_app ua on up.userId = ua.userId
        <where>
            up.profitDate = #{entity.profitDate}
            <if test="entity.userId != null">
                and up.userId = #{entity.userId}
            </if>
            <if test="entity.parentId != null">
                and up.parentId = #{entity.parentId}
            </if>
        </where>
        ORDER BY up.teamTransFlow DESC, up.id desc
    </select>
</mapper>
