<?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.ctop.finance.settlement.mapper.ReportSettlementMapper">

    <!-- 获取头条 账户 日流水信息 -->
    <select id="getAccountTransactionDayBytedance" resultType="org.jeecg.ctop.finance.settlement.entity.vo.SettlementReportBytedanceVo">
        select t.*,c.auth_name as accountName from (select id,
        advertiser_id,
        date,DATE_FORMAT(date,'%Y-%m') as uploadYears,
        balance,cash_cost,cost,frozen,income,reward_cost,shared_wallet_cost,transfer_in,transfer_out,create_time,
        (
        SELECT
        DISTINCT ag.agent_id
        from
        ctop_cwjs_agent_info ag
        LEFT JOIN ctop_agent_account ac on ag.agent_id = ac.agent_id
        where ag.`status` = 0
        and ag.enabled = 0
        and ac.account_id = #{accountId} limit 1) as agentId,
        (
        SELECT
        DISTINCT ag.name
        from
        ctop_cwjs_agent_info ag
        LEFT JOIN ctop_agent_account ac on ag.agent_id = ac.agent_id
        where ag.`status` = 0
        and ag.enabled = 0
        and ac.account_id = #{accountId} limit 1) as agentName
         from
        ctop_account_transaction_day
        where cost >0
        and advertiser_id = #{accountId}
            )t
      LEFT JOIN ctop_user_allocation c on t.advertiser_id = c.account_id
        where uploadYears = #{uploadYears}
        ORDER BY date
    </select>
<!-- 获取头条 账户 日流水信息 汇总页数据 -->
    <select id="getAccountTransactionDayBytedanceSheet" resultType="java.util.LinkedHashMap">
        SELECT
            t.advertiser_id accountId,
        ua.auth_name accountName,
        ROUND( SUM( t.balance ), 3 ) balance,
            ROUND( SUM( t.cash_cost ), 3 ) cash_cost,
            ROUND( SUM( t.cost ), 3 ) cost,
            ROUND( SUM( t.frozen ), 3 ) frozen,
            ROUND( SUM( t.income ), 3 ) income,
            ROUND( SUM( t.reward_cost ), 3 ) reward_cost,
            ROUND( SUM( t.shared_wallet_cost ), 3 ) shared_wallet_cost,
            ROUND( SUM( t.transfer_in ), 3 ) transfer_in,
            ROUND( SUM( t.transfer_out ), 3 ) transfer_out,
            ag.agent_id agentId,
            ag.NAME agentName,
        ua.project_id,
        (SELECT ad.id from ctop_advertiser ad WHERE ad.id = ua.advertiser_id) advertiserId,
        (SELECT ad.name from ctop_advertiser ad WHERE ad.id = ua.advertiser_id) advertiserName,
        (SELECT pd.id from ctop_project pr, ctop_product pd WHERE ua.project_id = pr.id and pr.product_id = pd.id) productId,
        (SELECT pd.product_name from ctop_project pr, ctop_product pd WHERE ua.project_id = pr.id and pr.product_id = pd.id) productName


        FROM
            ctop_account_transaction_day t
        LEFT JOIN (SELECT DISTINCT account_id,agent_id from ctop_agent_account where 1 = 1
        <if test='accountIds != null and accountIds.size() > 0'>
            AND account_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
            ) ac ON t.advertiser_id = ac.account_id
                LEFT JOIN ctop_cwjs_agent_info ag ON ag.agent_id = ac.agent_id
                LEFT JOIN ctop_user_allocation ua on ua.account_id = t.advertiser_id
        WHERE
            t.cost > 0
          AND DATE_FORMAT( t.date, '%Y-%m' ) = #{uploadYears}
        <if test='accountIds != null and accountIds.size() > 0'>
            AND t.advertiser_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
            t.advertiser_id
    </select>

    <!-- 查询 头条 账户id-->
    <select id="getAccountIdBytedance" resultType="java.lang.String">
        SELECT
        c.account_id AS accountId
        FROM
        ctop_cwjs_settlement_info c
        LEFT JOIN ctop_user_allocation u ON c.account_id = u.account_id
        WHERE
        del_flag = 0
        <if test="advertiserId !=null and advertiserId != ''">
            AND c.advertiser_id = #{advertiserId}
        </if>
        <if test="productId !=null and productId != ''">
            AND c.product_id = #{productId}
        </if>
        <if test="projectId !=null and projectId != ''">
            AND u.project_id = #{projectId}
        </if>
        <if test="mediaId !=null and mediaId != ''">
            AND c.media_id = #{mediaId}
        </if>
        <if test='sales !=null and sales.size() >0'>
            AND create_user_id in
            <foreach collection="sales" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

    </select>


    <!-- 查询  账户id 消耗不为0-->
    <select id="getAccountIdBytedanceRepeat" resultType="java.lang.String">
        SELECT
        advertiser_id
        FROM
        ctop_account_transaction_day
        WHERE
        cost > 0
    <if test="uploadYears !=null and uploadYears != ''">
        AND DATE_FORMAT( date, '%Y-%m' ) =  #{uploadYears}
    </if>
    <if test='accountIds !=null and accountIds.size() >0'>
        AND advertiser_id in
        <foreach collection="accountIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </if>
        GROUP BY
        advertiser_id
    </select>

    <!-- 查询  账户id 消耗不为0-->
    <select id="getAccountIdKuaiShouRepeat" resultType="java.lang.String">
        SELECT
        account_id AS accountId
        FROM
        ctop_kuaishou_daily_agent
        WHERE
        total_charged_in_yuan > 0
     <if test="uploadYears !=null and uploadYears != ''">
        AND DATE_FORMAT( date_time, '%Y-%m' ) =  #{uploadYears}
    </if>
    <if test='accountIds != null and accountIds.size() > 0'>
        AND account_id in
        <foreach collection="accountIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </if>
        GROUP BY
        account_id
    </select>











    <!-- 查询 头条 账户 图片-->
    <select id="getAccountImagesBytedance" resultType="java.lang.String">
        SELECT
        file_url as fileUrl
        FROM
        ctop_cwjs_settlement_file_info
        where del_flag = 0
        AND account_id = #{accountId}
        AND upload_years = #{uploadYears}
    </select>

    <!-- 获取快手 账户 日流水信息  账户维度-->
    <select id="getAccountTransactionDayKuaiShou" resultType="org.jeecg.ctop.finance.settlement.entity.vo.SettlementReportKuaiShouVo">
        select t.*,c.auth_name as accountName,
               base.user_id as ksId from (select id,
                                                           account_id,
                                                        date,DATE_FORMAT(date,'%Y-%m') as uploadYears,
                                                        daily_charge,real_charged,contract_rebate_real_charged,direct_rebate_real_charged,
                                                        daily_transfer_in,daily_transfer_out,balance,
                                                        real_recharged,contract_rebate_real_recharged,direct_rebate_real_recharged,
                                                      create_time
                                                    from
                                                        ctop_kuaishou_daily_flows
                                                    where account_id = #{accountId}
                                                   )t
                                                       LEFT JOIN ctop_user_allocation c on t.account_id = c.account_id
                                                       LEFT JOIN ctop_kuaishou_advertiser_base_info base on base.account_id = t.account_id
        where uploadYears = #{uploadYears}
    </select>

    <!-- 获取快手 账户 日流水信息  代理商维度-->
    <select id="getAccountTransactionDayKuaiShouAgent" resultType="org.jeecg.ctop.finance.settlement.entity.vo.SettlementReportKuaiShouVo">

        SELECT
            t.*
        FROM
            (
                SELECT
                    account_id,
                    date_time AS date,
		DATE_FORMAT( date_time, '%Y-%m' ) AS uploadYears,
		user_id AS ksId,
		total_charged_in_yuan AS dailyCharge,
		contract_rebate_real_charged_in_yuan AS contractRebateRealCharged,
		direct_rebate_real_charged_in_yuan as directRebateRealCharged,
		(real_charged_in_yuan + total_rebate_real_charged_in_yuan + credit_real_charged_in_yuan) as realCharged,
		account_name as accountName,
		create_time
                FROM
                    ctop_kuaishou_daily_agent
                WHERE
                    account_id = #{accountId}
            ) t
        WHERE
            t.uploadYears = #{uploadYears}
        ORDER BY
            t.date
    </select>

    <!-- 获取快手 账户 日流水信息  代理商 和 账户 维度-->
    <select id="getTransactionDayKuaiShouAgentAndAccount" resultType="org.jeecg.ctop.finance.settlement.entity.vo.SettlementReportKuaiShouVo">
        SELECT
            *
        FROM
            (
                SELECT
                    t.*
                FROM
                    (
                        SELECT
                            date_time as date,
                            account_id AS accountId,
                            total_charged_in_yuan as dailyCharge,
                            contract_rebate_real_charged_in_yuan as contractRebateRealCharged,
                            direct_rebate_real_charged_in_yuan as directRebateRealCharged,
                            ( real_charged_in_yuan + total_rebate_real_charged_in_yuan + credit_real_charged_in_yuan ) realCharged,
                            account_name AS accountName,
                            user_id AS ksId
                        FROM
                            ctop_kuaishou_daily_agent
                        WHERE
                           total_charged_in_yuan >0
                          and account_id = #{accountId}
                    ) t
                WHERE
                    DATE_FORMAT( t.date, '%Y-%m' ) = #{uploadYears}
                ORDER BY
                    t.date
                    LIMIT 31
            ) a
                LEFT JOIN (
                SELECT
                    t.*,
                    c.auth_name AS accountNameF,
                    base.user_id AS ksIdF
                FROM
                    (
                        SELECT
                            account_id AS accountIdF,
                            date,
                            daily_transfer_in as dailyTransferIn,
                            daily_transfer_out as dailyTransferOut,
                            balance as balance,
                            real_recharged as realRecharged,
                            contract_rebate_real_recharged as contractRebateRealRecharged,
                            direct_rebate_real_recharged as directRebateRealRecharged
                        FROM
                            ctop_kuaishou_daily_flows
                        WHERE
                            account_id = #{accountId}
                    ) t
                        LEFT JOIN ctop_user_allocation c ON t.accountIdF = c.account_id
                        LEFT JOIN ctop_kuaishou_advertiser_base_info base ON base.account_id = t.accountIdF
                WHERE
                    DATE_FORMAT( t.date, '%Y-%m' ) = #{uploadYears}
                ORDER BY
                    date
                    LIMIT 31
            ) b ON a.accountId = b.accountIdF
                AND a.date = b.date
            LIMIT 31

    </select>


    <!-- 查询 头条利润表 所用账户-->
    <select id="getProfitBytedanceAccount" resultType="java.util.LinkedHashMap">
        SELECT
        d.advertiser_id as accountId
        FROM
        ctop_account_transaction_day d
        WHERE d.cost >0
    <if test="startTime !=null and startTime != ''">
        AND d.date &gt;= #{startTime}
    </if>
    <if test="endTime !=null and endTime != ''">
        AND d.date &lt;= #{endTime}
    </if>
        GROUP BY d.advertiser_id;
</select>

    <!-- 查询 头条利润表 所用账户-->
    <select id="getProfitBytedanceAccountCost" resultType="java.util.LinkedHashMap">
        SELECT
        d.advertiser_id as accountId,
        ROUND(SUM( d.cost ),2) AS cost,
        ROUND(SUM( d.cash_cost ),2) AS cashCost,
        ROUND(SUM( d.reward_cost ),2) as rewardCost,
        IFNULL((SELECT a.name from ctop_user_allocation u,ctop_advertiser a where d.advertiser_id = u.account_id and u.advertiser_id = a.id),(SELECT a.name
        from ctop_cwjs_settlement_info c ,ctop_advertiser a where c.account_id = d.advertiser_id and c.advertiser_id = a.id)) as advertiserName,
        IFNULL((SELECT
        p.product_name
        FROM ctop_user_allocation u,ctop_project pj,ctop_product p where d.advertiser_id = u.account_id and u.project_id = pj.id and pj.product_id = p.id
        ),(SELECT p.product_name
        FROM ctop_cwjs_settlement_info c,ctop_product p where d.advertiser_id = c.account_id and c.product_id = p.id)) productName
        FROM
        ctop_account_transaction_day d
<where>
    <if test="accountId !=null and accountId != ''">
        AND d.advertiser_id = #{accountId}
    </if>
    <if test="startTime !=null and startTime != ''">
        AND d.date &gt;= #{startTime}
    </if>
    <if test="endTime !=null and endTime != ''">
        AND d.date &lt;= #{endTime}
    </if>
</where>
        GROUP BY d.advertiser_id
        limit 1

</select>

    <!-- 查询 头条利润表 总花费-->
    <select id="getProfitBytedanceCost" resultType="java.util.LinkedHashMap">
        SELECT
        d.advertiser_id AS accountId,
        ROUND( SUM( d.balance ), 3 ) balance,
        ROUND( SUM( d.cash_cost ), 3 ) cashCost,
        ROUND( SUM( d.cost ), 3 ) cost,
        ROUND( SUM( d.frozen ), 3 ) frozen,
        ROUND( SUM( d.income ), 3 ) income,
        ROUND( SUM( d.reward_cost ), 3 ) rewardCost,
        ROUND( SUM( d.shared_wallet_cost ), 3 ) sharedWalletCost,
        ROUND( SUM( d.transfer_in ), 3 ) transferIn,
        ROUND( SUM( d.transfer_out ), 3 ) transferOut
        FROM
        ctop_account_transaction_day d
        <where>
            <if test="accountId !=null and accountId != ''">
                AND d.advertiser_id = #{accountId}
            </if>
            <if test="startTime !=null and startTime != ''">
                AND d.date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND d.date &lt;= #{endTime}
            </if>
        </where>

    </select>



    <!-- 查询 头条利润表  返点类型和比列-->
    <select id="getProfitBytedance" resultType="java.util.LinkedHashMap">
        SELECT t.* from (
        SELECT
        cp.rebate_type AS rebateType,
        cp.rebate_rate AS rebateRate,
        cp.approved_status AS approvedStatus,
        min(r.policy_date )as policyStartDate,
        max(r.policy_date ) as policyEndDate,
        cp.create_time as createTime
        FROM
        ctop_cwjs_policy_info cp
        RIGHT JOIN ctop_cwjs_slettement_report r on r.policy_id = cp.id and r.advertiser_name = cp.advertiser_name
        WHERE
        cp.del_flag = 0
        and cp.approved_status = 3
        <if test="mediaType !=null and mediaType != ''">
            AND cp.media_type = #{mediaType}
        </if>
            AND r.advertiser_name = #{advertiserName}
            AND r.product_name = #{productName}
        <if test="saleId !=null and saleId != ''">
            AND r.create_user_id = #{saleId}
        </if>
        <if test="startTime !=null and startTime != ''">
            AND  r.policy_date &gt;= #{startTime}
        </if>
        <if test="endTime !=null and endTime != ''">
            AND r.policy_date &lt;= #{endTime}
        </if>
        GROUP BY r.policy_id
        ) t
    </select>


    <!-- 查询 利润表  返点类型和比列-->
    <select id="getProfitRebate" resultType="java.util.LinkedHashMap">
        SELECT
        p.id,
        p.rebate_type AS rebateType,
        p.rebate_rate AS rebateRate
        FROM
        ctop_cwjs_slettement_report r
        LEFT JOIN ctop_cwjs_policy_info p ON r.policy_id = p.id
        WHERE
        p.del_flag = 0
        <if test="mediaType !=null and mediaType != ''">
            AND p.media_type = #{mediaType}
        </if>
        AND r.advertiser_name = #{advertiserName}
        AND r.product_name = #{productName}
          <if test="saleId !=null and saleId != ''">
              AND r.create_user_id = #{saleId}
          </if>

        <if test="startTime !=null and startTime != ''">
            AND  r.policy_date &gt;= #{startTime}
        </if>
        <if test="endTime !=null and endTime != ''">
            AND r.policy_date &lt;= #{endTime}
        </if>
        GROUP BY
        p.id
        limit 1
    </select>

    <!-- 查询 利润表  销售人员姓名 -->
    <select id="getProfitReportSale" resultType="java.util.LinkedHashMap">
        ( SELECT
        p.sale_id AS saleId,
        y.realname AS saleName
        FROM
        ctop_user_allocation u
        LEFT JOIN ctop_project p ON u.project_id = p.id
        LEFT JOIN sys_user y ON y.id = p.sale_id
        WHERE
        u.account_id = #{accountId}
        LIMIT 1
        )
        UNION ALL
        (
        SELECT
        f.create_user_id,
        u.realname AS saleName
        FROM
        ctop_cwjs_settlement_info f
        LEFT JOIN sys_user u ON u.id = f.create_user_id
        WHERE
        f.del_flag = 0
        AND f.account_id = #{accountId}
        LIMIT 1
        )
        LIMIT 1
    </select>

    <!-- 查询 头条利润表  广告主和运营和运营所属区域-->
    <select id="getProfitBytedanceOperate" resultType="java.util.LinkedHashMap">
        SELECT
        c.create_user_id AS operateUserId,
        p.user_name AS operateName,
        IFNULL( p.nick_name, '' ) AS operateArea,
        d.NAME AS advertiserName
        FROM
        ctop_cwjs_settlement_file_info c
        LEFT JOIN user_company p on c.create_user_id = p.user_id
        LEFT JOIN ctop_advertiser d on d.id = c.advertiser_id
        where c.del_flag = 0
        <if test="accountId !=null and accountId != ''">
            AND c.account_id = #{accountId}
        </if>
        limit 1
    </select>

    <!-- 查询 快手 代理商 所有账户-->
    <select id="getProfitKuaiShouAgentAccount" resultType="java.util.LinkedHashMap">
        SELECT k.* from
        ctop_kuaishou_daily_agent k
        where k.total_charged_in_yuan !=0
            <if test="startTime !=null and startTime != ''">
                AND k.date_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND k.date_time &lt;= #{endTime}
            </if>
        GROUP BY k.account_id
    </select>

    <!-- 查询 快手 利润表 总花费-->
    <select id="getProfitKuaiShouAccountCost" resultType="java.util.LinkedHashMap">
        SELECT
        d.account_id as accountId,
        d.user_id AS ksId,
        round(SUM(d.total_charged_in_yuan),3) AS cost,
        round(SUM((d.real_charged_in_yuan + d.total_rebate_real_charged_in_yuan + d.credit_real_charged_in_yuan)),3) as cashCost,
        IFNULL((SELECT a.name from ctop_user_allocation u,ctop_advertiser a where d.account_id = u.account_id and u.advertiser_id = a.id),(SELECT a.name
        from ctop_cwjs_settlement_info c ,ctop_advertiser a where c.account_id = d.account_id and c.advertiser_id = a.id)) as advertiserName,
        IFNULL((SELECT
        p.product_name
        FROM ctop_user_allocation u,ctop_project pj,ctop_product p where d.account_id = u.account_id and u.project_id = pj.id and pj.product_id = p.id
        ),(SELECT p.product_name
        FROM ctop_cwjs_settlement_info c,ctop_product p where d.account_id = c.account_id and c.product_id = p.id)) productName
        FROM
        ctop_kuaishou_daily_agent d
        <where>
            <if test="accountId !=null and accountId != ''">
                AND d.account_id = #{accountId}
            </if>
            <if test="startTime !=null and startTime != ''">
                AND d.date_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND d.date_time &lt;= #{endTime}
            </if>
        </where>
        GROUP BY d.account_id
        limit 1


    </select>

    <!-- 查询 快手 利润表 总花费 结算单-->
    <select id="getProfitKuaiShouAccountCostSheet" resultType="java.util.LinkedHashMap">
        SELECT
        d.account_id as accountId,
        (SELECT ua.auth_name from ctop_user_allocation ua where ua.account_id = d.account_id) as accountName,
        d.user_id AS ksId,
        round(SUM(d.total_charged_in_yuan),3) AS cost,
        round(SUM((d.real_charged_in_yuan + d.total_rebate_real_charged_in_yuan + d.credit_real_charged_in_yuan)),3) as cashCost,
        IFNULL((SELECT a.name from ctop_user_allocation u,ctop_advertiser a where d.account_id = u.account_id and u.advertiser_id = a.id),(SELECT a.name
        from ctop_cwjs_settlement_info c ,ctop_advertiser a where c.account_id = d.account_id and c.advertiser_id = a.id)) as advertiserName,
        IFNULL((SELECT
        p.product_name
        FROM ctop_user_allocation u,ctop_project pj,ctop_product p where d.account_id = u.account_id and u.project_id = pj.id and pj.product_id = p.id
        ),(SELECT p.product_name
        FROM ctop_cwjs_settlement_info c,ctop_product p where d.account_id = c.account_id and c.product_id = p.id)) productName
        FROM
        ctop_kuaishou_daily_agent d
        <where>
            AND account_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
            and d.total_charged_in_yuan > 0
            <if test="startTime !=null and startTime != ''">
                AND d.date_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND d.date_time &lt;= #{endTime}
            </if>
        </where>
        GROUP BY d.account_id
    </select>

    <!-- 查询 快手 利润表 总花费-->
    <select id="getProfitKuaiShouCost" resultType="java.util.LinkedHashMap">
        SELECT
        d.account_id,
        d.user_id AS ksId,
        SUM(d.total_charged_in_yuan) AS cost,
        SUM((d.real_charged_in_yuan + d.total_rebate_real_charged_in_yuan + d.credit_real_charged_in_yuan)) as cashCost
        FROM
        ctop_kuaishou_daily_agent d
        <where>
            <if test="accountId !=null and accountId != ''">
                AND d.account_id = #{accountId}
            </if>
            <if test="startTime !=null and startTime != ''">
                AND d.date_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND d.date_time &lt;= #{endTime}
            </if>
        </where>
    </select>


    <!-- 查询 利润表  运营和运营所属区域-->
    <select id="getProfitReportOperateAndArea" resultType="java.util.LinkedHashMap">
        <!--SELECT
        c.create_user_id AS operateUserId,
        p.user_name AS operateName,
        IFNULL( p.nick_name, '' ) AS operateArea,
        d.NAME AS advertiserName
        FROM
        ctop_cwjs_settlement_file_info c
        LEFT JOIN user_company p on c.create_user_id = p.user_id
        LEFT JOIN ctop_advertiser d on d.id = c.advertiser_id
        where c.del_flag = 0
            <if test="accountId !=null and accountId != ''">
                AND c.account_id = #{accountId}
            </if>
            limit 1-->

        ( SELECT
        u.user_id AS operateUserId,
        u.user_name AS operateName,
        IFNULL( p.nick_name, '' ) AS operateArea
        FROM
        ctop_user_allocation u
        LEFT JOIN user_company p ON u.user_id = p.user_id
        WHERE
        u.account_id = #{accountId}
        LIMIT 1
        ) UNION ALL
        (
        SELECT
        c.create_user_id AS operateUserId,
        p.user_name AS operateName,
        IFNULL( p.nick_name, '' ) AS operateArea
        FROM
        ctop_cwjs_settlement_file_info c
        LEFT JOIN user_company p ON c.create_user_id = p.user_id
        WHERE
        c.del_flag = 0
        AND c.account_id = #{accountId}
        LIMIT 1
        )
        LIMIT 1
    </select>

    <!--  查询 所有已绑定用户的运营人员id -->
    <select id="getAccountOperateUserId" resultType="java.util.LinkedHashMap">
        SELECT user_id userId,user_name userName
        from ctop_user_allocation
        GROUP BY user_id
    </select>

    <!-- 查询运营人员 绑定的账户 以及产品 -->
    <select id="getAccountAndProductByOperate" resultType="java.util.LinkedHashMap">
        SELECT
            c.user_id as userId,
            c.user_name as userName,
            c.account_id as accountId,
            c.project_id as projectId,
            p.product_id as productId,
            d.product_name as productName
        FROM
            ctop_user_allocation c
                LEFT JOIN ctop_project p ON c.project_id = p.id
                LEFT JOIN ctop_product d ON p.product_id = d.id
        WHERE
            c.account_status = 0
        AND p.product_id IS NOT NULL
	AND c.user_id = #{userId}
    </select>





    <!-- 获取快手 账户 日流水信息  代理商 和 账户 结算单 -汇总页-->
    <select id="getTransactionDayKuaiShouSheet" resultType="org.jeecg.ctop.finance.settlement.entity.vo.SheetKuaiShouVo">
        SELECT
            *
        FROM
            (
                SELECT
                    t.*
                FROM
                    (
                        SELECT
                            date_time as date,
                            account_id AS accountId,
                            total_charged_in_yuan as dailyCharge,
                            contract_rebate_real_charged_in_yuan as contractRebateRealCharged,
                            direct_rebate_real_charged_in_yuan as directRebateRealCharged,
                            ( real_charged_in_yuan + total_rebate_real_charged_in_yuan + credit_real_charged_in_yuan ) realCharged,
                            account_name AS accountName,
                            user_id AS ksId
                        FROM
                            ctop_kuaishou_daily_agent
                        WHERE
                            total_charged_in_yuan >0
                          and account_id = #{accountId}
                    ) t
                WHERE
                   t.date &gt;= #{startTime}
                  AND t.date &lt;= #{endTime}
                ORDER BY
                    t.date
                    LIMIT 31
            ) a
                LEFT JOIN (
                SELECT
                    t.*,
                    c.auth_name AS accountNameF,
                    base.user_id AS ksIdF
                FROM
                    (
                        SELECT
                            account_id AS accountIdF,
                            date,
                            daily_transfer_in as dailyTransferIn,
                            daily_transfer_out as dailyTransferOut,
                            balance as balance,
                            real_recharged as realRecharged,
                            contract_rebate_real_recharged as contractRebateRealRecharged,
                            direct_rebate_real_recharged as directRebateRealRecharged
                        FROM
                            ctop_kuaishou_daily_flows
                        WHERE
                            account_id = #{accountId}
                    ) t
                        LEFT JOIN ctop_user_allocation c ON t.accountIdF = c.account_id
                        LEFT JOIN ctop_kuaishou_advertiser_base_info base ON base.account_id = t.accountIdF
                WHERE
                   t.date &gt;= #{startTime}
                  AND t.date &lt;= #{endTime}
                ORDER BY
                    date
                    LIMIT 31
            ) b ON a.accountId = b.accountIdF
                AND a.date = b.date
            LIMIT 31

    </select>








</mapper>
