<?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,
               tt.active
        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
                 LEFT JOIN (
            SELECT account_id,
                   SUM(active) as 'active',
                   stat_datetime
            FROM application.bytedance_advertiser_report_daily_dw
            WHERE account_id = #{accountId}
              AND stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d')
              AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
            GROUP BY stat_datetime
        ) tt ON DATE_FORMAT(t.date, '%Y%m%d') = tt.stat_datetime
        where date >= #{startTime}
          AND date &lt;= #{endTime}
        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 t.date >= #{startTime}
        AND t.date &lt;= #{endTime}
        <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="endTime !=null and startTime != ''">
            AND date >= #{startTime}
            AND date &lt;= #{endTime}
        </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="endTime !=null and startTime != ''">
            AND date_time >= #{startTime}
            AND date_time &lt;= #{endTime}
        </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 = DATE_FORMAT(#{time}, '%Y-%m')
    </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,
                     advertiser_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
                  `application`.kuaishou_advertiser_fund_daily_flows
              where advertiser_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 advertiser_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
                         `application`.kuaishou_advertiser_fund_daily_flows
                     WHERE
                         advertiser_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( MIN( 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="getBytedanceActive" resultType="String">
        SELECT SUM(active) as'active'
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE account_id = #{accountId}
        <if test="startTime !=null and startTime != ''">
            AND stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d')
        </if>
        <if test="endTime !=null and endTime != ''">
            AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        </if>
    </select>

    <select id="getKuaiShouActivation" resultType="String">
        SELECT SUM(activation) as'activation'
        FROM application.kuaishou_account_report_daily_dw
        WHERE account_id = #{accountId}
        <if test="startTime !=null and startTime != ''">
            AND stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d')
        </if>
        <if test="endTime !=null and endTime != ''">
            AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        </if>
    </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
        ORDER BY rebate_rate desc
        limit 1
        ) 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.date_time as 'time',
        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 *,
               IFNULL(b.balance1, 0) as balance
        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 advertiser_id AS accountIdF,
                            daily_charge  as flowDailyCharge,
                         date,
                         daily_transfer_in as dailyTransferIn,
                         daily_transfer_out as dailyTransferOut,
                         IFNULL(balance, 0) as balance1,
                         real_recharged as realRecharged,
                         contract_rebate_real_recharged as contractRebateRealRecharged,
                         direct_rebate_real_recharged as directRebateRealRecharged
                     FROM
                         `application`.kuaishou_advertiser_fund_daily_flows
                     WHERE
                         advertiser_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>
