<?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.IntranetSettlementMapper">


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

        SELECT *
        FROM (
                 SELECT t.*
                 FROM (
                          SELECT date_format(date_time, '%Y%m%d') 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 `jeecg-boot`.ctop_kuaishou_daily_agent
                          WHERE total_charged_in_yuan > 0
                            and account_id = #{accountId}
                      ) t
                 WHERE t.date >= DATE_FORMAT(#{startTime}, '%Y%m%d')
                   and t.date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
                 ORDER BY t.date
             ) 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_format(date, '%Y%m%d') as '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 `jeecg-boot`.ctop_user_allocation c ON t.accountIdF = c.account_id
                     LEFT JOIN `jeecg-boot`.ctop_kuaishou_advertiser_base_info base ON base.account_id = t.accountIdF
            WHERE DATE_FORMAT(t.date, '%Y-%m-%d') >= #{startTime}
              AND DATE_FORMAT(t.date, '%Y-%m-%d') &lt;= #{endTime}
            ORDER BY
                date
        ) b ON a.accountId = b.accountIdF
            AND a.date = b.date
                 LEFT JOIN (
        SELECT account_id as 'accountIdC',
               SUM(activation) as'activation',
        date_format(stat_date, '%Y%m%d') as 'date'
        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>
        GROUP BY stat_date
        ORDER BY stat_date
            ) c ON a.accountId = c.accountIdC
        AND a.date = c.date
    </select>
</mapper>

