<?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.material.mapper.SysManagerCompanyMapper">

    <select id="queryDepartByParentId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id, depart_name
        FROM `jeecg-boot`.sys_depart
        WHERE parent_id = #{designId}
        ORDER BY depart_order
    </select>

    <select id="queryAllPersonnel" resultType="string">
        SELECT DISTINCT lvl FROM application.app_user_lvl_info
        <if test='list!=null and list.size>0'>
            where dep_id in
            <foreach collection="list" item="obj" separator=","
                     open="(" close=")">
                #{obj.id}
            </foreach>
        </if>
    </select>

    <select id="queryPersonnels" resultType="string">
        SELECT DISTINCT lvl
        FROM application.app_user_lvl_info
        where dep_id = #{id}
    </select>

    <select id="queryMaterialNumber" resultType="long">
        SELECT COUNT(1) FROM `jeecg-boot`.ctop_material_info t1
        INNER JOIN `jeecg-boot`.ctop_material_ascription t2 ON t1.code = t2.material_id
        WHERE t1.create_time &gt;= #{startTime}
        AND t1.create_time &lt;= concat(#{endTime},' 23:59:59')
        AND t1.status != 2
        <if test='allPersonnel!=null and allPersonnel.size>0'>
            AND t2.clip_id in
            <foreach collection="allPersonnel" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="queryKsMaterialCharge" resultType="double">
        SELECT IFNULL(SUM(charge),0) FROM application.kuaishou_material_video_report_daily_dw
        WHERE stat_date &gt;= DATE_FORMAT(#{startTime}, '%Y%m%d')
        AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test='clipIds!=null and clipIds.size>0'>
            AND clip_id in
            <foreach collection="clipIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        <if test='userIds!=null and userIds.size>0'>
            AND user_id in
            <foreach collection="userIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
    </select>


    <select id="queryTtMaterialCharge" resultType="double">
        SELECT IFNULL(SUM(cost),0) FROM application.bytedance_material_video_report_daily_dw
        WHERE stat_datetime &gt;= DATE_FORMAT(#{startTime}, '%Y%m%d')
        AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test='clipIds!=null and clipIds.size>0'>
            AND clip_id in
            <foreach collection="clipIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        <if test='userIds!=null and userIds.size>0'>
            AND user_id in
            <foreach collection="userIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
    </select>


    <select id="queryTtProjectConsumeTop" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.project_name as 'projectName', t1.project_id as 'projectId', IFNULL(sum(cost), 0) as 'allCost',IFNULL(adNum, 0) as 'adNum'
        FROM application.bytedance_advertiser_report_hourly_dw t1
                 LEFT JOIN
             (SELECT project_id, IFNULL(SUM(new_ad_num), 0) as 'adNum'
              FROM application.app_bytedance_account_base_info_d
              WHERE project_id IN (
                  SELECT id
                  FROM `jeecg-boot`.ctop_project
                  WHERE media_id = 1
                    and company_id = #{companyId}
              )
                AND stat_date &gt;= DATE_FORMAT(#{startTime}, '%Y%m%d')
                AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
              GROUP BY project_id) t2 ON t1.project_id = t2.project_id
        WHERE t1.project_id IN (
            SELECT id
            FROM `jeecg-boot`.ctop_project
            WHERE media_id = 1
              and company_id = #{companyId}
        )
          AND t1.stat_datetime &gt;= DATE_FORMAT(#{startTime}, '%Y%m%d')
          AND t1.stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        GROUP BY t1.project_id
        HAVING sum(cost) >0
        ORDER BY allCost Desc LIMIT 10
    </select>

    <select id="queryKsProjectConsumeTop" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.project_name as 'projectName', t1.project_id as 'projectId', IFNULL(sum(charge), 0) as 'allCost',IFNULL(adNum, 0) as 'adNum'
        FROM application.kuaishou_account_report_hourly_dw t1
                 LEFT JOIN
             (SELECT project_id, SUM(new_unit_num) as 'adNum'
              FROM application.app_kuaishou_account_base_info_d
              WHERE project_id IN (
                  SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 2 and company_id = #{companyId}
              )
                AND stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d')
                AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
              GROUP BY project_id) t2 ON t1.project_id = t2.project_id
        WHERE t1.project_id IN (
            SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 2 and company_id = #{companyId}
        )
          AND t1.stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d')
          AND t1.stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        GROUP BY t1.project_id
        HAVING  sum(charge) >0
        ORDER BY allCost Desc LIMIT 10
    </select>

    <select id="getKsMonthlyData" resultType="Map">
        SELECT DATE_FORMAT(stat_date, '%Y-%m-%d') as 'statDate', sum(charge) as 'allCost'
        FROM application.kuaishou_material_video_report_daily_dw
        WHERE project_id IN (
            SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 2 and company_id = #{companyId}
        )
          AND stat_date >= #{startTime}
          AND stat_date &lt;= #{endTime}
        GROUP BY stat_date
        ORDER BY stat_date
    </select>

    <select id="getTtMonthlyData" resultType="Map">
        SELECT DATE_FORMAT(stat_datetime, '%Y-%m-%d') as 'statDate', sum(cost) as 'allCost'
        FROM application.bytedance_material_video_report_daily_dw
        WHERE project_id IN (
            SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 1 and company_id = #{companyId}
        )
          AND stat_datetime >= #{startTime}
          AND stat_datetime &lt;= #{endTime}
        GROUP BY stat_datetime
        ORDER BY stat_datetime
    </select>


    <select id="queryKsRealTimeCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.allCost as 'todayCost',t2.allCost as 'yesCost'
        FROM (SELECT SUM(charge) as 'allCost'
              FROM application.kuaishou_account_report_hourly_dw
              WHERE project_id IN
                    (SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 2 and company_id = #{companyId})
                AND stat_date = DATE_FORMAT(#{today}, '%Y%m%d')
                AND `hour` &lt;= #{hour}) t1
                 LEFT JOIN
             (SELECT SUM(charge) as 'allCost'
              FROM application.kuaishou_account_report_hourly_dw
              WHERE project_id IN
                    (SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 2 and company_id = #{companyId})
                AND stat_date = DATE_FORMAT(#{lastDay}, '%Y%m%d')
                AND `hour` &lt;= #{hour}) t2 ON 1 = 1
    </select>


    <select id="queryTtRealTimeCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (SELECT SUM(cost) as 'todayCost'
              FROM application.bytedance_advertiser_report_hourly_dw
              WHERE project_id IN
                    (SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 1 and company_id = #{companyId})
                AND stat_datetime = DATE_FORMAT(#{today}, '%Y%m%d')
                AND `hour` &lt;= #{hour}) t1
                 LEFT JOIN
             (SELECT SUM(cost) as 'yesCost'
              FROM application.bytedance_advertiser_report_hourly_dw
              WHERE project_id IN
                    (SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 1 and company_id = #{companyId})
                AND stat_datetime = DATE_FORMAT(#{lastDay}, '%Y%m%d')
                AND `hour` &lt;= #{hour}) t2 ON 1 = 1
    </select>

    <select id="queryKsRealTimeGroupNum" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
                 SELECT COUNT(1) as 'todayNum'
                 FROM media_api.kuaishou_ad_unit_list
                 WHERE advertiser_id IN (
                     SELECT account_id
                     FROM `jeecg-boot`.ctop_user_allocation t1
                              left JOIN `jeecg-boot`.ctop_project t2 ON t2.id = t1.project_id
                     WHERE t2.media_id = 2
                       and company_id = #{companyId}
                 )
                   AND create_time >= #{today}
             ) t1
                 LEFT JOIN
             (
                 SELECT SUM(new_unit_num) as 'yesNum'
                 FROM application.app_kuaishou_account_base_info_d
                 WHERE project_id IN (
                     SELECT id
                     FROM `jeecg-boot`.ctop_project
                     WHERE media_id = 2
                       and company_id = #{companyId}
                 )
                   AND stat_date = DATE_FORMAT(#{lastDay}, '%Y%m%d')
             ) t2 on 1 = 1
    </select>


    <select id="queryTtRealTimeGroupNum" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
                 SELECT COUNT(1) as 'todayNum'
                 FROM media_api.bytedance_ad_get
                 WHERE account_id IN (
                     SELECT account_id
                     FROM `jeecg-boot`.ctop_user_allocation t1
                              left JOIN `jeecg-boot`.ctop_project t2 ON t2.id = t1.project_id
                     WHERE t2.media_id = 1
                       and company_id = #{companyId}
                 )
                   AND create_time >= #{today}
             ) t1
                 LEFT JOIN
             (
                 SELECT SUM(new_ad_num) as 'yesNum'
                 FROM application.app_bytedance_account_base_info_d
                 WHERE project_id IN (
                     SELECT id
                     FROM `jeecg-boot`.ctop_project
                     WHERE media_id = 1
                       and company_id = #{companyId}
                 )
                   AND stat_date = DATE_FORMAT(#{lastDay}, '%Y%m%d')
             ) t2 on 1 = 1
    </select>


    <select id="queryKsRealTimeMaterialNum" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (SELECT COUNT(1) as 'todayCount'
        FROM `jeecg-boot`.ctop_material_info
        WHERE create_time >= #{today}
        AND user_id in
        <foreach collection="userIds" item="id" separator=","
                 open="(" close=")">
            #{id}
        </foreach>
        ) t1
        LEFT JOIN
        (
        SELECT sum(1) as 'yesCount' FROM application.app_kuaishou_material_info
        WHERE help_time = DATE_FORMAT(#{lastDay}, '%Y%m%d')
        and company_id = #{companyId}
        ) t2 ON 1 = 1
    </select>


    <select id="queryTtRealTimeMaterialNum" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (SELECT COUNT(1) as 'todayCount'
        FROM `jeecg-boot`.ctop_material_info
        WHERE create_time >= #{today}
        AND user_id in
        <foreach collection="userIds" item="id" separator=","
                 open="(" close=")">
            #{id}
        </foreach>
        ) t1
        LEFT JOIN
        (
        SELECT sum(1) as 'yesCount' FROM application.app_bytedance_material_info
        WHERE help_time = DATE_FORMAT(#{lastDay}, '%Y%m%d')
        and company_id = #{companyId}
        ) t2 ON 1 = 1
    </select>


    <select id="getKsTotalConsume" resultType="String">
        SELECT IFNULL(sum(charge), 0)
        FROM application.kuaishou_material_video_report_daily_dw
        WHERE project_id IN (
            SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 2 and company_id = #{companyId}
        )
          AND stat_date >= #{startTime}
          AND stat_date &lt;= #{endTime}
    </select>


    <select id="getTtTotalConsume" resultType="String">
        SELECT IFNULL(sum(cost), 0)
        FROM application.bytedance_material_video_report_daily_dw
        WHERE project_id IN (
            SELECT id FROM `jeecg-boot`.ctop_project WHERE media_id = 1 and company_id = #{companyId}
        )
          AND stat_datetime >= #{startTime}
          AND stat_datetime &lt;= #{endTime}
    </select>
</mapper>
