<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.pluss.platform.mapper.SpreadMapper">

    <select id="selectDirectUser" resultType="cn.pluss.platform.entity.UserApp">
        SELECT
            ua.userId,
            ua.parentId,
            ui.loginName,
            IFNULL(mr.rate, 38) rate,
            ui.phone,
            ua.userName,
            ua.createDt,
            mbi.alias,
            ua.agentStaffId,
            ua.`level`,
            ua.`logo`,
            mcs.remark auditReason,
            IF (
                (ua.bankStatus != 3 OR ua.bankStatus IS NULL), 1,
                CASE mcs.`status`
                WHEN '1' THEN IF(mcs.`thirdStatus` = '5', 6, 3)
                WHEN '8' THEN 6
                WHEN '2' THEN 4
                WHEN '5' THEN 4
                WHEN '3' THEN 5
                WHEN '4' THEN 5
                ELSE IF(mcs.`thirdStatus` = -100, 4, 2) END
            ) `status`
        FROM
            tb_pluss_user_app ua
        LEFT JOIN tb_pluss_merchant_rate mr ON ua.userId = mr.userId
        LEFT JOIN tb_pluss_user_info ui ON ui.id = ua.userId
        LEFT JOIN tb_pluss_merchant_base_info mbi ON ua.merchantCode = mbi.merchantCode
        LEFT JOIN
                (
                    SELECT mcs.merchantCode, mcs.remark, mcs.`status`, mcs.`thirdStatus` FROM tb_pluss_merchant_channel_status mcs
                    LEFT JOIN (
                        SELECT merchantCode, MAX(valid) maxValid, MIN(id) minId FROM tb_pluss_merchant_channel_status mcs
                        GROUP BY merchantCode
                    ) t ON mcs.merchantCode = t.merchantCode AND mcs.valid = t.maxValid
                    WHERE (t.maxValid = 1) OR
                    (t.maxValid = 0 AND t.minId = mcs.id)
                ) mcs ON mcs.merchantCode = ua.merchantCode
        <where>
            ua.parentId = #{condition.userId}
            AND LOCATE('@', ui.loginName) = 0
            AND LENGTH(ui.loginName) = 11
            <if test="condition.agentStaffIds != null and condition.agentStaffIds.size() > 0">
                AND ua.agentStaffId IN
                <foreach collection="condition.agentStaffIds" open="(" separator="," close=")" item="item">
                    #{item}
                </foreach>
            </if>
            <if test="condition.alias != null and condition.alias != ''">
                AND mbi.alias LIKE CONCAT('%', #{condition.alias}, '%')
            </if>
            <if test="condition.userName != null and condition.userName != ''">
                AND ua.userName LIKE CONCAT('%', #{condition.userName}, '%')
            </if>
            <if test="condition.phone != null and condition.phone != ''">
                AND ui.phone LIKE CONCAT('%', #{condition.phone}, '%')
            </if>
            <if test="condition.startTime != null and condition.startTime != ''">
                AND ua.createDt &gt;= #{condition.startTime}
            </if>
            <if test="condition.endTime != null and condition.endTime != ''">
                AND ua.createDt &lt; #{condition.endTime}
            </if>
            <if test="condition.status != null and condition.status != ''">
                <choose>
                    <when test='condition.status == "1"'>
                        AND (
                            ua.bankStatus != 3
                            OR ua.bankStatus IS NULL
                        )
                    </when>
                    <when test='condition.status == "3"'>
                        AND mcs.`status` = '1'
                    </when>
                    <when test='condition.status == "4"'>
                        AND (
                            mcs.`status` = '2'
                            OR (mcs.`status` = '0' AND mcs.`thirdStatus` != '-100')
                            OR mcs.`status` = '5'
                        )
                    </when>
                    <when test='condition.status == "5"'>
                        AND (
                            mcs.`status` = '3'
                            OR mcs.`status` = '4'
                        )
                    </when>
                    <when test='condition.status == "2"'>
                        AND (
                            ua.bankStatus = 3
                            AND
                            (
                                mcs.`status` = '0'
                                OR mcs.`status` IS NULL
                            )
                        )
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY ua.createDt DESC
    </select>

    <select id="selectPmtSubUser" resultType="cn.pluss.platform.entity.UserApp">
        SELECT
            ua.userId,
            ui.loginName,
            ua.parentId,
            IFNULL(mr.rate, 38) rate,
            ui.phone,
            ua.userName,
            ua.createDt,
            mbi.alias,
            ua.agentStaffId,
            ua.`level`,
            ua.`logo`,
            mcs.remark auditReason,
            IF (
                (ua.bankStatus != 3 OR ua.bankStatus IS NULL), 1,
                CASE mcs.`status`
                WHEN '1' THEN IF(mcs.`thirdStatus` = '5', 6, 3)
                WHEN '8' THEN 6
                WHEN '2' THEN 4
                WHEN '5' THEN 4
                WHEN '3' THEN 5
                WHEN '4' THEN 5
                ELSE IF(mcs.`thirdStatus` = -100, 4, 2) END
            ) `status`
        FROM
        (
            (
                SELECT ua1.userId
                FROM tb_pluss_user_app ua
                LEFT JOIN tb_pluss_user_app ua1
                ON ua.userId = ua1.parentId
                WHERE ua.parentId = #{condition.userId}
            )
            UNION
            (
                SELECT ua2.userId
                FROM tb_pluss_user_app ua
                LEFT JOIN tb_pluss_user_app ua1
                ON ua.userId = ua1.parentId
                LEFT JOIN tb_pluss_user_app ua2
                ON ua1.userId = ua2.parentId
                WHERE ua.parentId = #{condition.userId}
            )
        ) userIdList
        LEFT JOIN tb_pluss_user_app ua ON ua.userId = userIdList.userId
        LEFT JOIN tb_pluss_merchant_rate mr ON ua.userId = mr.userId
        LEFT JOIN tb_pluss_user_info ui ON ui.id = ua.userId
        LEFT JOIN tb_pluss_merchant_base_info mbi ON ua.merchantCode = mbi.merchantCode
        LEFT JOIN tb_pluss_merchant_channel_status mcs ON mcs.merchantCode = ua.merchantCode
        <where>
            LOCATE('@', ui.loginName) = 0
            AND LENGTH(ui.loginName) = 11
            <if test="condition.agentStaffIds != null and condition.agentStaffIds.size() > 0">
                AND ua.agentStaffId IN
                <foreach collection="condition.agentStaffIds" open="(" separator="," close=")" item="item">
                    #{item}
                </foreach>
            </if>
            <if test="condition.alias != null and condition.alias != ''">
                AND mbi.alias LIKE CONCAT('%', #{condition.alias}, '%')
            </if>
            <if test="condition.userName != null and condition.userName != ''">
                AND ua.userName LIKE CONCAT('%', #{condition.userName}, '%')
            </if>
            <if test="condition.phone != null and condition.phone != ''">
                AND ui.phone LIKE CONCAT('%', #{condition.phone}, '%')
            </if>
            <if test="condition.startTime != null and condition.startTime != ''">
                AND ua.createDt &gt;= #{condition.startTime}
            </if>
            <if test="condition.endTime != null and condition.endTime != ''">
                AND ua.createDt &lt; #{condition.endTime}
            </if>
            <if test="condition.status != null and condition.status != ''">
                <choose>
                    <when test='condition.status == "1"'>
                        AND (
                            ua.bankStatus != 3
                            OR ua.bankStatus IS NULL
                        )
                    </when>
                    <when test='condition.status == "3"'>
                        AND mcs.`status` = '1'
                    </when>
                    <when test='condition.status == "4"'>
                        AND (
                            mcs.`status` = '2'
                            OR mcs.`status` = '5'
                        )
                    </when>
                    <when test='condition.status == "5"'>
                        AND (
                            mcs.`status` = '3'
                            OR mcs.`status` = '4'
                        )
                    </when>
                    <when test='condition.status == "2"'>
                        AND (
                            ua.bankStatus = 3
                            AND (
                                mcs.`status` = '0'
                                OR mcs.`status` IS NULL
                            )
                        )
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY ua.createDt DESC
    </select>

    <select id="selectAgentSubUser" resultType="cn.pluss.platform.entity.UserApp">
        SELECT
            ua.userId,
            ua.parentId,
            ui.loginName,
            IFNULL(mr.rate, 38) rate,
            ui.phone,
            ua.userName,
            ua.createDt,
            mbi.alias,
            ua.agentStaffId,
            ua.`level`,
            ua.`logo`,
            mcs.remark auditReason,
            IF (
                (ua.bankStatus != 3 OR ua.bankStatus IS NULL), 1,
                CASE mcs.`status`
                WHEN '1' THEN 3
                WHEN '2' THEN 4
                WHEN '3' THEN 5
                ELSE 2 END
            ) `status`
        FROM
        (
            (
                SELECT ua1.userId
                FROM tb_pluss_user_app ua
                LEFT JOIN tb_pluss_user_app ua1
                ON ua.userId = ua1.parentId
                WHERE ua.parentId = #{condition.userId}
                AND ua.userType = 'agent'
                AND LOCATE(#{condition.userId}, ua.pIdArr ) > 0
            )
            UNION
            (
                SELECT ua1.userId
                FROM tb_pluss_user_app ua
                LEFT JOIN tb_pluss_user_app ua1
                ON ua.userId = ua1.parentId
                WHERE ua.parentId = #{condition.userId}
                AND ua.userType = 'promoter'
            )
            UNION
            (
                SELECT ua2.userId
                FROM tb_pluss_user_app ua
                LEFT JOIN tb_pluss_user_app ua1
                ON ua.userId = ua1.parentId
                LEFT JOIN tb_pluss_user_app ua2
                ON ua1.userId = ua2.parentId
                WHERE ua.parentId = #{condition.userId}
                AND ua.userType = 'promoter'
            )
        ) userIdList
        LEFT JOIN tb_pluss_user_app ua ON ua.userId = userIdList.userId
        LEFT JOIN tb_pluss_merchant_rate mr ON ua.userId = mr.userId
        LEFT JOIN tb_pluss_user_info ui ON ui.id = ua.userId
        LEFT JOIN tb_pluss_merchant_base_info mbi ON ua.merchantCode = mbi.merchantCode
        LEFT JOIN tb_pluss_merchant_channel_status mcs ON mcs.merchantCode = ua.merchantCode
        <where>
            LOCATE('@', ui.loginName) = 0
            AND LENGTH(ui.loginName) = 11
            <if test="condition.agentStaffIds != null and condition.agentStaffIds.size() > 0">
                AND ua.agentStaffId IN
                <foreach collection="condition.agentStaffIds" open="(" separator="," close=")" item="item">
                    #{item}
                </foreach>
            </if>
            <if test="condition.alias != null and condition.alias != ''">
                AND mbi.alias LIKE CONCAT('%', #{condition.alias}, '%')
            </if>
            <if test="condition.userName != null and condition.userName != ''">
                AND ua.userName LIKE CONCAT('%', #{condition.userName}, '%')
            </if>
            <if test="condition.phone != null and condition.phone != ''">
                AND ui.phone LIKE CONCAT('%', #{condition.phone}, '%')
            </if>
            <if test="condition.startTime != null and condition.startTime != ''">
                AND ua.createDt &gt;= #{condition.startTime}
            </if>
            <if test="condition.endTime != null and condition.endTime != ''">
                AND ua.createDt &lt; #{condition.endTime}
            </if>
            <if test="condition.status != null and condition.status != ''">
                <choose>
                    <when test='condition.status == "1"'>
                        AND (
                            ua.bankStatus != 3
                            OR ua.bankStatus IS NULL
                        )
                    </when>
                    <when test='condition.status == "3"'>
                        AND mcs.`status` = '1'
                    </when>
                    <when test='condition.status == "4"'>
                        AND (
                            mcs.`status` = '2'
                            OR mcs.`status` = '5'
                        )
                    </when>
                    <when test='condition.status == "5"'>
                        AND (
                            mcs.`status` = '3'
                            OR mcs.`status` = '4'
                        )
                    </when>
                    <when test='condition.status == "2"'>
                        AND (
                            ua.bankStatus = 3
                            AND (
                                mcs.`status` = '0'
                                OR mcs.`status` IS NULL
                            )
                        )
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY ua.createDt DESC
    </select>

    <!-- 查询团队交易数据 -->
    <select id="userTemaAchiePage" resultType="cn.pluss.platform.vo.UserTeamAchieVO">
        SELECT
        <choose>
            <when test="condition.dataType == null or condition.dataType == 1">
                <if test="condition.dateType == null or condition.dateType == 1">
                    SUM(ownTransFlow) as tradeAmt ,SUM(transNum) as tradeNum,profitDate as `date`,userId, 1 as dateType
                </if>
                <if test="condition.dateType == 2">
                    SUM(ownTransFlow) as tradeAmt,SUM(transNum) as tradeNum,profitMonth as `date`,userId,2 as dateType
                </if>
                <if test="condition.dateType == 3">
                    SUM(ownTransFlow) as tradeAmt,SUM(transNum) as tradeNum,DATE_FORMAT(profitDate,'%Y') as `date`,userId,3 as dateType
                </if>
            </when>
            <otherwise>
                <if test="condition.dateType == null or condition.dateType == 1">
                    (teamTransFlow) as tradeAmt ,transNum as tradeNum,profitDate as `date`,userId, 1 as dateType
                </if>
                <if test="condition.dateType == 2">
                    SUM(teamTransFlow) as tradeAmt,SUM(transNum) as tradeNum,profitMonth as `date`,userId,2 as dateType
                </if>
                <if test="condition.dateType == 3">
                    SUM(teamTransFlow) as tradeAmt,SUM(transNum) as tradeNum,DATE_FORMAT(profitDate,'%Y') as `date`,userId,3 as dateType
                </if>
            </otherwise>
        </choose>

        from  tb_pluss_user_profit where
        <choose>
            <when test="condition.dataType == null or condition.dataType == 1">
                parentId = #{condition.userId} and  ownTransFlow != 0 and teamTransFlow = 0
            </when>
            <otherwise>
                userId = #{condition.userId}
            </otherwise>
        </choose>
        <if test="condition.dateType == null or condition.dateType == 1">
            group by profitDate
        </if>
        <if test="condition.dateType == 2">
            group by profitMonth
        </if>
        <if test="condition.dateType == 3">
            group by DATE_FORMAT(profitDate,'%Y')
        </if>
        order by
        <if test="condition.dateType == null or condition.dateType == 1">
             profitDate
        </if>
        <if test="condition.dateType == 2">
             profitMonth
        </if>
        <if test="condition.dateType == 3">
             DATE_FORMAT(profitDate,'%Y')
        </if>
        desc

    </select>

    <select id="selectTodaySpreadData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT
            IFNULL(SUM(IF(mo.`status` = 1, mo.consumeFee, IF(mo.`status` = 2, -mo.consumeFee, 0))), 0.00) ownAmt,
            COUNT(DISTINCT IF(mo.`status` = 1, mo.orderNumber, 0)) ownBillCount,
            COUNT(DISTINCT IF(mo.`status` = 1 || mo.`status` = 2, mo.merchantCode, 0)) ownActiveCount,
            COUNT(DISTINCT IF(ua.createDt > CURRENT_DATE, ua.userId, 0)) ownNewCount,
            CURRENT_DATE date
        FROM tb_pluss_user_app ua
             LEFT JOIN tb_pluss_user_app sua ON ua.userId = sua.parentId
             LEFT JOIN tb_pluss_merchant_order mo ON mo.merchantCode = ua.merchantCode AND mo.createDate = CURRENT_DATE
        WHERE ua.parentId = #{userId} AND ua.userType = 'promoter' AND ua.userType = 'promoter'
    </select>

    <select id="pageDaySpreadPageData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT startDate date, COUNT(DISTINCT IF(createDate &lt;= startDate, merchantCode, NULL)) ownActiveCount
                , COUNT(DISTINCT IF(createDate = startDate, merchantCode, NULL)) ownNewCount
                , SUM(amount) ownAmt, SUM(count) ownBillCount
        FROM (
            SELECT DISTINCT DATE_FORMAT(ua.createDt, '%Y-%m-%d') createDate, ua.merchantCode, ua.userId, mbs.amount, mbs.count, mbs.startDate
            FROM tb_pluss_user_app ua
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON ua.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_bill_statistics mbs ON mbs.userId = ua.userId
            WHERE ua.parentId = #{userId} AND mbs.type = 1 AND mcs.status = 3
        ) t  GROUP BY startDate
    </select>

    <select id="pageMonthSpreadPageData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT startDate month, COUNT(DISTINCT IF(createDate &lt;= startDate, merchantCode, NULL)) ownActiveCount, SUM(amount) ownAmt, SUM(count) ownBillCount
                              , COUNT(DISTINCT IF(createDate = startDate, merchantCode, NULL)) ownNewCount
        FROM (
            SELECT DISTINCT DATE_FORMAT(ua.createDt, '%Y-%m') createDate, ua.merchantCode, ua.userId, mbs.amount, mbs.count, DATE_FORMAT(mbs.startDate, '%Y-%m') startDate
            FROM tb_pluss_user_app ua
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON ua.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_bill_statistics mbs ON mbs.userId = ua.userId
            WHERE ua.parentId = #{userId} AND mbs.type = 1 AND mcs.status = 3
        ) t  GROUP BY startDate
    </select>

    <select id="pageYearSpreadPageData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT startDate year, COUNT(DISTINCT IF(createDate &lt;= startDate, merchantCode, NULL)) ownActiveCount, SUM(amount) ownAmt, SUM(count) ownBillCount
                             , COUNT(DISTINCT IF(createDate = startDate, merchantCode, NULL)) ownNewCount
        FROM (
            SELECT DISTINCT DATE_FORMAT(ua.createDt, '%Y') createDate, ua.merchantCode, ua.userId, mbs.amount, mbs.count, DATE_FORMAT(mbs.startDate, '%Y') startDate
            FROM tb_pluss_user_app ua
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON ua.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_bill_statistics mbs ON mbs.userId = ua.userId
            WHERE ua.parentId = #{userId} AND mbs.type = 1 AND mcs.status = 3
            ) t  GROUP BY startDate
    </select>

    <select id="pageTeamDaySpreadPageData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT startDate date, COUNT(DISTINCT IF(createDate &lt; startDate, merchantCode, NULL)) teamActiveCount, SUM(amount) teamAmt, SUM(count) teamBillCount
                            , COUNT(DISTINCT IF(createDate = startDate, merchantCode, NULL)) teamNewCount
        FROM (
            SELECT DISTINCT DATE_FORMAT(ua2.createDt, '%Y-%m-%d') createDate, ua2.merchantCode, ua2.userId, mbs.amount, mbs.count, mbs.startDate FROM tb_pluss_user_app ua
            LEFT JOIN tb_pluss_user_app ua2 ON ua2.parentId = ua.userId
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON ua.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_bill_statistics mbs ON mbs.userId = ua.userId
            WHERE ua.parentId = #{userId} AND mbs.type = 1 AND mcs.status = 3
        ) t  GROUP BY startDate
    </select>

    <select id="pageTeamMonthSpreadPageData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT startDate month, COUNT(DISTINCT IF(createDate &lt; startDate, merchantCode, NULL)) teamActiveCount, SUM(amount) teamAmt, SUM(count) teamBillCount
                              , COUNT(DISTINCT IF(createDate = startDate, merchantCode, NULL)) teamNewCount
        FROM (
            SELECT DISTINCT DATE_FORMAT(ua2.createDt, '%Y-%m') createDate, ua2.merchantCode, ua2.userId, mbs.amount, mbs.count, DATE_FORMAT(mbs.startDate, '%Y-%m') startDate
            FROM tb_pluss_user_app ua
            LEFT JOIN tb_pluss_user_app ua2 ON ua2.parentId = ua.userId
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON ua.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_bill_statistics mbs ON mbs.userId = ua.userId
            WHERE ua.parentId = #{userId} AND mbs.type = 1 AND mcs.status = 3
            ) t  GROUP BY startDate
    </select>

    <select id="pageTeamYearSpreadPageData" resultType="cn.pluss.platform.vo.SpreadData">
        SELECT startDate year, COUNT(DISTINCT IF(createDate &lt; startDate, merchantCode, NULL)) teamActiveCount, SUM(amount) teamAmt, SUM(count) teamBillCount
                             , COUNT(DISTINCT IF(createDate = startDate, merchantCode, NULL)) teamNewCount
        FROM (
            SELECT DISTINCT DATE_FORMAT(ua2.createDt, '%Y') createDate, ua2.merchantCode, ua2.userId, mbs.amount, mbs.count, DATE_FORMAT(mbs.startDate, '%Y') startDate
            FROM tb_pluss_user_app ua
            LEFT JOIN tb_pluss_user_app ua2 ON ua2.parentId = ua.userId
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON ua.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_bill_statistics mbs ON mbs.userId = ua.userId
            WHERE ua.parentId = #{userId} AND mbs.type = 1 AND mcs.status = 3
            ) t  GROUP BY startDate
    </select>

    <select id="getAppUserTeanFlow" parameterType="cn.pluss.platform.vo.UserTeamAchieVO" resultType="cn.pluss.platform.vo.UserTeamAchieVO">
        SELECT
        <choose>
            <when test="dataType == null or dataType == 1">
                SUM(ownTransFlow) as tradeAmt,SUM(transNum) as tradeNum
            </when>
            <otherwise>
                SUM(teamTransFlow) as tradeAmt ,SUM(transNum) as tradeNum
            </otherwise>
        </choose>
        from  tb_pluss_user_profit
        where
        <choose>
            <when test="dataType == null or dataType == 1">
                parentId = #{userId} and  ownTransFlow != 0 and teamTransFlow = 0
            </when>
            <otherwise>
                userId = #{userId}
            </otherwise>
        </choose>
        <if test="dateType == null or dateType == 1">
            and profitDate = #{date}
        </if>
        <if test="dateType == 2">
            and profitMonth = #{date}
        </if>
        <if test="dateType == 3">
            and DATE_FORMAT(profitDate,'%Y') = #{date}
        </if>
    </select>


</mapper>