<?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.modules.ctop.mapper.MediaReportMapper">
    <select id="queryAdvertiserNameByCompanyId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id,
               name
        from ctop_advertiser
        where company_id = #{companyId}
    </select>

    <select id="queryProjectNameByAdvertiserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id,
               project_name
        from ctop_project
        where advertiser_id = #{advertiserId}
    </select>

    <select id="queryAllBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT e.*
        from (
                 SELECT r.*, (r.todayCost - r.yesterdayCost) as diffCost, (r.todayCost + r.yesterdayCost) as sumCost

                 from (
                          SELECT t2.media_id                                                           as mediaType,
                                 t1.company_id                                                         as companyId,
                                 t1.id                                                                 as advertiserId,
                                 t1.NAME                                                               as advertiserName,
                                 t2.id                                                                 as projectId,
                                 t2.project_name                                                       as projectName,
                                 t2.responsible_name                                                   as operatorManger,
                                 t2.design_responsible_name                                            as designManger,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_user_allocation t3
                                  where t2.id = t3.project_id)                                         as operator,
                                 (SELECT realname from sys_user u WHERE u.id = t1.sale_id)             as sale,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'plan')                                            as plan,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'shot')                                            as shot,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'clip')                                            as clip,
                                 IFNULL((SELECT sum(cost)
                                         from ctop_bytedance_report_advertiser_daily report
                                         where report.advertiser_id in (SELECT account_id
                                                                        from ctop_user_allocation t3
                                                                        where t2.id = t3.project_id)
                                           and report.stat_datetime = '${todayDate} 00:00:00'), 0)     as todayCost,
                                 IFNULL((SELECT sum(cost)
                                         from ctop_bytedance_report_advertiser_daily report
                                         where report.advertiser_id in (SELECT account_id
                                                                        from ctop_user_allocation t3
                                                                        where t2.id = t3.project_id)
                                           and report.stat_datetime = '${yesterdayDate} 00:00:00'), 0) as yesterdayCost
                          FROM ctop_advertiser t1
                                   LEFT JOIN ctop_project t2 ON t1.id = t2.advertiser_id
                          where t2.media_id in(1,3)
                            and t2.id is not null) as r

                 UNION

                 SELECT r.*, (r.todayCost - r.yesterdayCost) as diffCost, (r.todayCost + r.yesterdayCost) as sumCost
                 from (
                          SELECT t2.media_id                                               as mediaType,
                                 t1.company_id                                             as companyId,
                                 t1.id                                                     as advertiserId,
                                 t1.NAME                                                   as advertiserName,
                                 t2.id                                                     as projectId,
                                 t2.project_name                                           as projectName,
                                 t2.responsible_name                                       as operatorManger,
                                 t2.design_responsible_name                                as designManger,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_user_allocation t3
                                  where t2.id = t3.project_id)                             as operator,
                                 (SELECT realname from sys_user u WHERE u.id = t1.sale_id) as sale,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'plan')                                as plan,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'shot')                                as shot,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'clip')                                as clip,
                                 IFNULL((SELECT sum(charge)
                                         from ctop_kuaishou_report_daily_account report
                                         where report.account_id in (SELECT account_id
                                                                     from ctop_user_allocation t3
                                                                     where t2.id = t3.project_id)
                                           and report.stat_date = '${todayDate}'), 0)      as todayCost,
                                 IFNULL((SELECT sum(charge)
                                         from ctop_kuaishou_report_daily_account report
                                         where report.account_id in (SELECT account_id
                                                                     from ctop_user_allocation t3
                                                                     where t2.id = t3.project_id)
                                           and report.stat_date = '${yesterdayDate}'), 0)  as yesterdayCost
                          FROM ctop_advertiser t1
                                   LEFT JOIN ctop_project t2 ON t1.id = t2.advertiser_id
                          where t2.media_id  in(2,4)
                            and t2.id is not null) as r

                 union

                 SELECT r.*, (r.todayCost - r.yesterdayCost) as diffCost, (r.todayCost + r.yesterdayCost) as sumCost
                 from (
                          SELECT '-'                                                            as mediaType,
                                 '-'                                                             as companyId,
                                 id                                                              as advertiserId,
                                 corporation_name                                                as advertiserName,
                                 '-'                                                             as projectId,
                                 account_name                                                    as projectName,
                                 '-'                                                             as operatorManger,
                                 '-'                                                             as designManger,
                                 '-'                                                             as operator,
                                 '-'                                                             as sale,
                                 '-'                                                             as plan,
                                 '-'                                                             as shot,
                                 '-'                                                             as clip,
                                 IFNULL((SELECT sum(balance)
                                         from ctop_kuaishou_agent_account report
                                         where report.id = t1.id
                                           and report.create_time like '%${todayDate}%'), 0)     as todayCost,
                                 IFNULL((SELECT sum(balance)
                                         from ctop_kuaishou_agent_account report
                                         where report.id = t1.id
                                           and report.create_time like '%${yesterdayDate}%'), 0) as yesterdayCost
                          from ctop_kuaishou_agent_account t1
                          where t1.create_time like '%${yesterdayDate}%'
                          GROUP by advertiserName, projectName) as r) as e
        ORDER BY sumCost desc
    </select>

    <select id="queryMediaReportBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT e.*
        FROM (
                 SELECT r.*, (r.todayCost - r.yesterdayCost) as diffCost, (r.todayCost + r.yesterdayCost) as sumCost

                 from (
                          SELECT t2.media_id                                                           as mediaType,
                                 t1.company_id                                                         as companyId,
                                 t1.id                                                                 as advertiserId,
                                 t1.NAME                                                               as advertiserName,
                                 t2.id                                                                 as projectId,
                                 t2.project_name                                                       as projectName,
                                 t2.responsible_name                                                   as operatorManger,
                                 t2.design_responsible_name                                            as designManger,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_user_allocation t3
                                  where t2.id = t3.project_id)                                         as operator,
                                 (SELECT realname from sys_user u WHERE u.id = t1.sale_id)             as sale,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'plan')                                            as plan,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'shot')                                            as shot,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'clip')                                            as clip,
                                 IFNULL((SELECT sum(cost)
                                         from ctop_bytedance_report_advertiser_daily report
                                         where report.advertiser_id in (SELECT account_id
                                                                        from ctop_user_allocation t3
                                                                        where t2.id = t3.project_id)
                                           and report.stat_datetime = '${todayDate} 00:00:00'), 0)     as todayCost,
                                 IFNULL((SELECT sum(cost)
                                         from ctop_bytedance_report_advertiser_daily report
                                         where report.advertiser_id in (SELECT account_id
                                                                        from ctop_user_allocation t3
                                                                        where t2.id = t3.project_id)
                                           and report.stat_datetime = '${yesterdayDate} 00:00:00'), 0) as yesterdayCost
                          FROM ctop_advertiser t1
                                   LEFT JOIN ctop_project t2 ON t1.id = t2.advertiser_id
                          where t2.media_id  in(1,3)
                            and t2.id is not null) as r

                 UNION

                 SELECT r.*, (r.todayCost - r.yesterdayCost) as diffCost, (r.todayCost + r.yesterdayCost) as sumCost
                 from (
                          SELECT t2.media_id                                               as mediaType,
                                 t1.company_id                                             as companyId,
                                 t1.id                                                     as advertiserId,
                                 t1.NAME                                                   as advertiserName,
                                 t2.id                                                     as projectId,
                                 t2.project_name                                           as projectName,
                                 t2.responsible_name                                       as operatorManger,
                                 t2.design_responsible_name                                as designManger,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_user_allocation t3
                                  where t2.id = t3.project_id)                             as operator,
                                 (SELECT realname from sys_user u WHERE u.id = t1.sale_id) as sale,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'plan')                                as plan,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'shot')                                as shot,
                                 (SELECT GROUP_CONCAT(DISTINCT user_name)
                                  from ctop_project_member member
                                  where t2.id = member.project_id
                                    and role_code = 'clip')                                as clip,
                                 IFNULL((SELECT sum(charge)
                                         from ctop_kuaishou_report_daily_account report
                                         where report.account_id in (SELECT account_id
                                                                     from ctop_user_allocation t3
                                                                     where t2.id = t3.project_id)
                                           and report.stat_date = '${todayDate}'), 0)      as todayCost,
                                 IFNULL((SELECT sum(charge)
                                         from ctop_kuaishou_report_daily_account report
                                         where report.account_id in (SELECT account_id
                                                                     from ctop_user_allocation t3
                                                                     where t2.id = t3.project_id)
                                           and report.stat_date = '${yesterdayDate}'), 0)  as yesterdayCost
                          FROM ctop_advertiser t1
                                   LEFT JOIN ctop_project t2 ON t1.id = t2.advertiser_id
                          where t2.media_id  in(2,4)
                            and t2.id is not null) as r) as e
        where companyId = ${condition}
        ORDER BY sumCost desc
    </select>

    <select id="queryAgentReportBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT e.*
        from (
                 SELECT r.*, (r.todayCost - r.yesterdayCost) as diffCost, (r.todayCost + r.yesterdayCost) as sumCost
                 from (
                          SELECT '-'                                                             as mediaType,
                                 '-'                                                             as companyId,
                                 id                                                              as advertiserId,
                                 corporation_name                                                as advertiserName,
                                 '-'                                                             as projectId,
                                 account_name                                                    as projectName,
                                 '-'                                                             as operatorManger,
                                 '-'                                                             as designManger,
                                 '-'                                                             as operator,
                                 '-'                                                             as sale,
                                 '-'                                                             as plan,
                                 '-'                                                             as shot,
                                 '-'                                                             as clip,
                                 IFNULL((SELECT sum(balance)
                                         from ctop_kuaishou_agent_account report
                                         where report.id = t1.id
                                           and report.create_time like '%${todayDate}%'), 0)     as todayCost,
                                 IFNULL((SELECT sum(balance)
                                         from ctop_kuaishou_agent_account report
                                         where report.id = t1.id
                                           and report.create_time like '%${yesterdayDate}%'), 0) as yesterdayCost
                          from ctop_kuaishou_agent_account t1
                          where t1.create_time like '%${yesterdayDate}%'
                          GROUP by advertiserName, projectName) as r) as e
        ORDER BY sumCost desc
    </select>

    <!-- 快手 -->
    <select id="kuaishouMediaReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        mediaType,
        advertiserName,
        productName,
        sale,
        operator,
        format(todayCost,3) as todayCost,
        format(yesterdayCost,3) as yesterdayCost,
        format(( todayCost - yesterdayCost ),3) AS diffCost,
        companyName
        FROM
                (
                    SELECT
                    a.media_id AS mediaType,
                    a.advertiser_name AS advertiserName,
                    a.product_name AS productName,
                    (
                        SELECT
                            GROUP_CONCAT( DISTINCT o.realname )
                        FROM
                            ctop_product_project_map m
                            LEFT JOIN ctop_project n ON m.project_id = n.id
                            LEFT JOIN sys_user o ON n.sale_id = o.id
                        WHERE
                            m.product_id = a.id
                    ) AS sale,
                    (
                        SELECT
                            GROUP_CONCAT( DISTINCT al.user_name )
                        FROM
                            ( SELECT * FROM ctop_user_allocation GROUP BY account_id ) al
                            LEFT JOIN user_company co ON al.user_id = co.user_id
                        WHERE
                            al.project_id IN ( SELECT project_id FROM ctop_product_project_map map WHERE map.product_id = a.id )
                            AND co.company_id = d.company_id
                    ) AS operator,
                    IFNULL(
                            (
                                SELECT
                                    sum( charge )
                                FROM
                                    ctop_kuaishou_report_daily_account report
                                WHERE
                                    report.account_id IN (
                                SELECT
                                    account_id
                                FROM
                                    ( SELECT * FROM ctop_user_allocation GROUP BY account_id ) al
                                    LEFT JOIN user_company co ON al.user_id = co.user_id
                                WHERE
                                    al.project_id IN ( SELECT project_id FROM ctop_product_project_map map WHERE map.product_id = a.id )
                                    AND co.company_id = d.company_id
                                    )
                                    AND report.stat_date = #{today}
                            )
                    ,0) AS todayCost,
                    IFNULL(
                            (
                                SELECT
                                    sum( charge )
                                FROM
                                    ctop_kuaishou_report_daily_account report
                                WHERE
                                    report.account_id IN (
                                SELECT
                                    account_id
                                FROM
                                    ctop_user_allocation al
                                    LEFT JOIN user_company co ON al.user_id = co.user_id
                                WHERE
                                    al.project_id IN ( SELECT project_id FROM ctop_product_project_map map WHERE map.product_id = a.id )
                                    AND co.company_id = d.company_id
                                    )
                                    AND report.stat_date = #{yesterday}
                             )
                        ,0) AS yesterdayCost,
                        case
                            when d.company_id = 'd57fecdcf7a94d009736d9c850731582' then '华北'
                            when d.company_id = '6608ed13c0dd42de93c790dbdf124234' then '华南'
                            when d.company_id = '4b10089775c040119e139087517aed88' then '华东'
                            else '-' end AS companyName
                    FROM
                        ctop_product a
                        LEFT JOIN ctop_product_project_map b ON a.id = b.product_id
                        LEFT JOIN ( SELECT * FROM ctop_user_allocation GROUP BY account_id ) c ON b.project_id = c.project_id
                        LEFT JOIN user_company d ON c.user_id = d.user_id
                    WHERE
                        d.company_id IS NOT NULL
                        AND a.product_status = 1
                        AND a.media_id = 2
                        <if test="advertiserId != null and advertiserId != ''">
                        AND a.advertiser_id = #{advertiserId}
                        </if>
                        <if test="companyId != null and companyId != ''">
                            AND d.company_id = #{companyId}
                        </if>
                        <if test="productId != null and productId != ''">
                            AND a.id = #{productId}
                        </if>
                    GROUP BY
                        a.id,
                        d.company_id
                ) t
        ORDER BY
        t.companyName,t.todayCost DESC
    </select>

    <select id="toutiaoMediaReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        mediaType,
        advertiserName,
        productName,
        sale,
        operator,
        format(todayCost,2) as todayCost,
        format(yesterdayCost,2) as yesterdayCost,
        format(( todayCost - yesterdayCost ),2) AS diffCost,
        companyName
        FROM
            (
                SELECT
                    a.media_id AS mediaType,
                    a.advertiser_name AS advertiserName,
                    a.product_name AS productName,
                    (
                SELECT
                    GROUP_CONCAT( DISTINCT o.realname )
                FROM
                    ctop_product_project_map m
                    LEFT JOIN ctop_project n ON m.project_id = n.id
                    LEFT JOIN sys_user o ON n.sale_id = o.id
                WHERE
                    m.product_id = a.id
                    ) AS sale,
                    (
                SELECT
                    GROUP_CONCAT( DISTINCT al.user_name )
                FROM
                    ctop_user_allocation al
                    LEFT JOIN user_company co ON al.user_id = co.user_id
                WHERE
                    al.project_id IN ( SELECT project_id FROM ctop_product_project_map map WHERE map.product_id = a.id )
                    AND co.company_id = d.company_id
                    ) AS operator,
                    IFNULL(
                    (
                SELECT
                    sum( cost )
                FROM
                    ctop_bytedance_report_advertiser_daily report
                WHERE
                    report.advertiser_id IN (
                SELECT
                    account_id
                FROM
                    ctop_user_allocation al
                    LEFT JOIN user_company co ON al.user_id = co.user_id
                WHERE
                    al.project_id IN ( SELECT project_id FROM ctop_product_project_map map WHERE map.product_id = a.id )
                    AND co.company_id = d.company_id
                    )
                    AND date_format( report.stat_datetime, '%Y-%m-%d' ) = #{today}
                    ),
                    0
                    ) AS todayCost,
                    IFNULL(
                            (
                        SELECT
                            sum( cost )
                        FROM
                            ctop_bytedance_report_advertiser_daily report
                        WHERE
                            report.advertiser_id IN (
                                SELECT
                                    account_id
                                FROM
                                    ctop_user_allocation al
                                    LEFT JOIN user_company co ON al.user_id = co.user_id
                                WHERE
                                    al.project_id IN ( SELECT project_id FROM ctop_product_project_map map WHERE map.product_id = a.id )
                                    AND co.company_id = d.company_id
                            )
                            AND date_format( report.stat_datetime, '%Y-%m-%d' ) = #{yesterday}
                            ),0) AS yesterdayCost,
                    case when d.company_id = 'd57fecdcf7a94d009736d9c850731582' then '华北'
                         when d.company_id = '6608ed13c0dd42de93c790dbdf124234' then '华南'
                         when d.company_id = '4b10089775c040119e139087517aed88' then '华东'
                         else '-' end AS companyName
                FROM
                    ctop_product a
                    LEFT JOIN ctop_product_project_map b ON a.id = b.product_id
                    LEFT JOIN ( SELECT * FROM ctop_user_allocation GROUP BY account_id ) c ON b.project_id = c.project_id
                    LEFT JOIN user_company d ON c.user_id = d.user_id
                WHERE
                    d.company_id IS NOT NULL
                    AND a.media_id = 1
                    AND a.product_status = 1
                    <if test="advertiserId != null and advertiserId != ''">
                        AND a.advertiser_id = #{advertiserId}
                    </if>
                    <if test="companyId != null and companyId != ''">
                        AND d.company_id = #{companyId}
                    </if>
                    <if test="productId != null and productId != ''">
                        AND a.id = #{productId}
                    </if>
                GROUP BY
                    a.id,
                    d.company_id
            ) t
        ORDER BY t.companyName, t.todayCost DESC

    </select>

    <select id="getAllProductList" resultType="cn.com.ctop.common.module.entity.Product">
        select
        id as id,
        product_name as productName,
        media_id as mediaId
        from
        ctop_product
        where
        product_status = 1
        <if test="mediaType != null">
            and media_id = #{mediaType}
        </if>
        <if test="advertiserId != null">
            and advertiser_id = #{advertiserId}
        </if>
    </select>

</mapper>
