<?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.com.ctop.common.module.mapper.AccountCompensationMapper">

    <select id="getAllOperatorList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.user_id as 'userId',
        t2.realname as 'userName',
        t.role_name as 'roleName',
        t.role_code as 'roleCode',
        t4.company_name as 'companyName'
        FROM `sys_role` t
        LEFT JOIN sys_user_role t1 ON t.id = t1.role_id
        LEFT JOIN sys_user t2 ON t1.user_id = t2.id
        LEFT JOIN ctop_user_allocation t3 ON t1.user_id = t3.user_id
        LEFT JOIN user_company t4 on t4.user_id = t1.user_id
        WHERE t.role_code in
        <foreach collection="roleCode" item="roleCode" separator="," open="(" close=")">
            #{roleCode}
        </foreach>
        AND t3.media_id = #{mediaId}
        GROUP BY t1.user_id
        UNION
        SELECT
        t1.user_id as 'userId',
        t2.realname as 'userName',
        t.role_name as 'roleName',
        t.role_code as 'roleCode',
        t4.company_name as 'companyName'
        FROM `sys_role` t
        LEFT JOIN sys_user_role t1 ON t.id = t1.role_id
        LEFT JOIN sys_user t2 ON t1.user_id = t2.id
        LEFT JOIN ctop_transfer_account t5 ON t1.user_id = t5.transferor
        LEFT JOIN ctop_user_allocation t3 ON t3.account_id = t5.account_id
        LEFT JOIN user_company t4 on t4.user_id = t1.user_id
        WHERE  t.role_code in
        <foreach collection="roleCode" item="roleCode" separator="," open="(" close=")">
            #{roleCode}
        </foreach>
        AND t3.media_id = #{mediaId}
        GROUP BY t1.user_id

    </select>

    <select id="getCompMoney" resultType="java.math.BigDecimal">
        SELECT
        comp_money
        FROM ctop_account_compensation
        WHERE user_id = #{userId}
        <if test="startDate != null">
            AND start_date >=#{startDate}
        </if>
        <if test="endDate != null">
            AND end_date &lt;=#{endDate}
        </if>
    </select>

    <select id="getAccountUser" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.user_id,realname
        FROM ctop_user_allocation t1
                 LEFT JOIN sys_user t2 ON t1.user_id = t2.id
        WHERE t1.account_id = #{accountId}
    </select>

    <select id="queryAccountCompensation" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t1.user_id as 'userId',
            t1.start_date as 'startDate',
            t1.end_date as 'endDate',
            t1.account_id as 'accountId',
            t1.project_id as 'projectId',
            t1.comp_money as 'compMoney',
            t1.media_id as 'mediaId',
            t1.create_by as 'createBy',
            t1.create_time as 'createTime',
            t2.realname,
            t3.auth_name
        FROM `ctop_account_compensation` t1
                 LEFT JOIN sys_user t2 ON t1.user_id = t2.id
                 LEFT JOIN ctop_user_allocation t3 ON t1.account_id = t3.account_id
        ORDER BY t1.create_time desc
    </select>
</mapper>