<?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="cn.com.ctop.kuaishou.modules.report.mapper.NewMonitorMapper">
    <resultMap id="BaseResultMap" type="cn.com.ctop.kuaishou.modules.report.entity.NewMonitorInfo">
        <id column="id" property="user_id"/>
        <result column="realname" property="user_name"/>
        <result column="leader_id" property="parent_id"/>
        <collection property="children" ofType="cn.com.ctop.kuaishou.modules.report.entity.NewMonitorInfo"
                    select="cn.com.ctop.kuaishou.modules.report.mapper.NewMonitorMapper.getOperator" column="id">
        </collection>
    </resultMap>

    <select id="getOperator" resultMap="BaseResultMap" parameterType="String">
        SELECT id, realname, leader_id
        FROM sys_user
        WHERE leader_id = #{userId};
    </select>

    <select id="getAllAccountId" resultType="Long">
        SELECT DISTINCT t.account_id as 'accountId'
        from ctop_user_allocation t
        where account_status = 0
          and media_id = #{type}
    </select>
    <select id="getKuaiShouGroup" resultType="Map">
        SELECT t1.account_id as 'accountId',t1.auth_name as 'accountName',t1.user_name as 'userName',t.unit_id as
        'unitId',t.group_create_time as 'groupCreatTime',DATE_FORMAT(DATE_ADD(t.group_create_time,INTERVAL 7
        DAY),'%Y-%m-%d') as 'afterTime'
        from ctop_user_allocation t1 LEFT JOIN ctop_kuaishou_group t on t1.account_id=t.account_id
        <where>
            and t.account_id=#{accountId}
            <choose>
                <when test="date!= null and date!=''">
                    and t.group_create_time &gt;= DATE_SUB(#{date},INTERVAL 7 DAY)
                </when>
                <otherwise>
                    and t.group_create_time &gt;= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR),'%Y-%m-%d')
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="getKuaiShouGroupNow" resultType="Map">
        SELECT t1.account_id as 'accountId',t1.auth_name as 'accountName',t1.user_name as 'userName',t.unit_id as
        'unitId',t.group_create_time as 'groupCreatTime',DATE_FORMAT(DATE_ADD(t.group_create_time,INTERVAL 7
        DAY),'%Y-%m-%d') as 'afterTime'
        from ctop_user_allocation t1 LEFT JOIN ctop_kuaishou_group t on t1.account_id=t.account_id
        <where>
            and t.account_id=#{accountId}
            and t.group_create_time &gt;= #{date}
        </where>
    </select>

    <select id="getKuaiShouCampaign" resultType="Map">
        SELECT t1.account_id as 'accountId',t1.auth_name as 'accountName',t1.user_name as 'userName',t.campaign_id as
        'campaignId',t.put_create_time as 'campaignCreatTime',DATE_FORMAT(DATE_ADD(t.put_create_time,INTERVAL 7
        DAY),'%Y-%m-%d') as 'afterTime'
        from ctop_user_allocation t1 LEFT JOIN ctop_kuaishou_campaign t on t1.account_id=t.account_id
        <where>
            and t.account_id=#{accountId}
            <choose>
                <when test="date != null and date !=''">
                    and t.put_create_time &gt;= DATE_SUB(#{date},INTERVAL 7 DAY)
                </when>
                <otherwise>
                    and t.put_create_time &gt;= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR),'%Y-%m-%d')
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="getKuaiShouCampaignNow" resultType="Map">
        SELECT t1.account_id as 'accountId',t1.auth_name as 'accountName',t1.user_name as 'userName',t.campaign_id as
        'campaignId',t.put_create_time as 'campaignCreatTime',DATE_FORMAT(DATE_ADD(t.put_create_time,INTERVAL 7
        DAY),'%Y-%m-%d') as 'afterTime'
        from ctop_user_allocation t1 LEFT JOIN ctop_kuaishou_campaign t on t1.account_id=t.account_id
        <where>
            and t.account_id=#{accountId}
            and t.put_create_time &gt;=#{date}
        </where>
    </select>

    <select id="getTouTiaoCampaign" resultType="Map">
        SELECT t1.account_id as 'accountId',t1.auth_name as 'accountName',t1.user_name as 'userName',t.id as
        'campaignId',t.ad_create_time as 'campaignCreatTime',DATE_FORMAT(DATE_ADD(t.ad_create_time,INTERVAL 7
        DAY),'%Y-%m-%d') as 'afterTime'
        from ctop_user_allocation t1 LEFT JOIN ctop_bytedance_advertise_plan t on t1.account_id=t.account_id
        <where>
            and t.account_id=#{accountId}
            <choose>
                <when test="date != null and date !=''">
                    and t.ad_create_time &gt;= DATE_SUB(#{date},INTERVAL 7 DAY)
                </when>
                <otherwise>
                    and t.ad_create_time &gt;= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR),'%Y-%m-%d')
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="getTouTiaoCampaignNow" resultType="Map">
        SELECT t1.account_id as 'accountId',t1.auth_name as 'accountName',t1.user_name as 'userName',t.id as
        'campaignId',t.ad_create_time as 'campaignCreatTime',DATE_FORMAT(DATE_ADD(t.ad_create_time,INTERVAL 7
        DAY),'%Y-%m-%d') as 'afterTime'
        from ctop_user_allocation t1 LEFT JOIN ctop_bytedance_advertise_plan t on t1.account_id=t.account_id
        <where>
            and t.account_id=#{accountId}
            and t.ad_create_time &gt;= #{date}
        </where>
    </select>

    <select id="getGroupCharge" resultType="java.math.BigDecimal">

        select sum(charge)
        from ctop_kuaishou_report_daily_group t
        where t.stat_date &lt; #{afterDate}
          and t.unit_id = #{unitId}
    </select>

    <select id="getCampaignCharge" resultType="java.math.BigDecimal">

        select sum(charge)
        from ctop_kuaishou_report_daily_campaign t
        where t.stat_date &lt; #{afterDate}
          and t.campaign_id = #{campaignId}
    </select>

    <select id="getCampaignChargeTouTiao" resultType="java.math.BigDecimal">

        select sum(cost)
        from ctop_bytedance_report_plan_daily t
        where t.stat_datetime &lt; #{afterDate}
          and t.ad_id = #{campaignId}
    </select>

    <insert id="saveMonitorGroup">
        insert into ctop_new_monitor_group(account_id, account_name, operating_name, unit_id, create_time, project_type,
                                           effective)
        values (#{accountId}, #{accountName}, #{operatingName}, #{unitId}, #{createTime}, #{projectType}, #{effective})
    </insert>

    <insert id="saveMonitorCampaign">
        insert into ctop_new_monitor_campaign (account_id, account_name, operating_name, campaign_id, create_time,
                                               project_type, effective)
        values (#{accountId}, #{accountName}, #{operatingName}, #{campaignId}, #{createTime}, #{projectType},
                #{effective})
    </insert>

    <select id="getMonitorGroupByUnitId" resultType="String">
        select t.effective
        from ctop_new_monitor_group t
        where t.unit_id = #{unitId}
    </select>

    <select id="getMonitorPlanByCampaignId" resultType="String">
        select t.effective
        from ctop_new_monitor_campaign t
        where t.campaign_id = #{campaignId}
    </select>

    <update id="updateMonitorGroupByUnitId">
        update ctop_new_monitor_group t
        set t.effective=#{effective}
        where t.unit_id = #{unitId}
    </update>

    <update id="updateMonitorPlanByCampaignId">
        update ctop_new_monitor_campaign t
        set t.effective=#{effective}
        where t.campaign_id = #{campaignId}
    </update>

    <select id="getKuaiShouGroupMessage" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id AS 'accountId',
        account_name AS 'authName',
        MAX(stat_date) AS 'maxCreateTime',
        '0' AS 'valid',
        IFNULL(SUM(new_unit_num),0) AS 'new',
        user_name AS 'userName'
        FROM
        application.app_kuaishou_account_base_info_d
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
        GROUP BY account_id
        order by `new` desc
    </select>


    <select id="getPlanMessage" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id AS 'accountId',
        account_name AS 'authName',
        MAX(stat_date) AS 'maxCreateTime',
        '0' AS 'valid',
        IFNULL(SUM(new_campaign_num),0) AS 'new',
        user_name AS 'userName'
        FROM
        application.app_kuaishou_account_base_info_d
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
        GROUP BY account_id
        order by `new` desc
    </select>

    <select id="getKuaiShouGroupSum" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(SUM(new_unit_num),0) AS 'new',
        '-' AS 'valid'
        FROM
        application.app_kuaishou_account_base_info_d
        where stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getPlanSum" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(SUM(new_campaign_num),0) AS 'new',
        '0' AS 'valid'
        FROM
        application.app_kuaishou_account_base_info_d
        where stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getKuaiShouGroupMessageGroup" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        <choose>
            <when test='group=="1"'>
                DATE_FORMAT(stat_date, '%Y-%m' ) AS 'statDate',
            </when>
            <otherwise>
                DATE_FORMAT(stat_date, '%Y-%m-%d' ) AS 'statDate',
            </otherwise>
        </choose>
        IFNULL(SUM(new_unit_num),0) AS 'new',
        '0' AS 'valid'
        FROM application.app_kuaishou_account_base_info_d
        where stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
        GROUP BY
        statDate
        order by statDate asc
    </select>

    <select id="getPlanMessageGroup" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        <choose>
            <when test='group=="1"'>
                DATE_FORMAT(stat_date, '%Y-%m') AS 'statDate',
            </when>
            <otherwise>
                DATE_FORMAT(stat_date, '%Y-%m-%d') AS 'statDate',
            </otherwise>
        </choose>
        IFNULL(SUM(new_campaign_num),0) AS 'new',
        '0' AS 'valid'
        FROM application.app_kuaishou_account_base_info_d
        where stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
        GROUP BY
        statDate
        order by statDate asc
    </select>

    <select id="getPlanSumBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(SUM(promotion_num),0) AS 'new',
        '0' AS 'valid'
        FROM
        application.app_bytedance_account_base_info_d
        where stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
    </select>


    <select id="getPlanMessageGroupBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        <choose>
            <when test='group=="1"'>
                DATE_FORMAT(stat_date, '%Y-%m') AS 'statDate',
            </when>
            <otherwise>
                DATE_FORMAT(stat_date, '%Y-%m-%d') AS 'statDate',
            </otherwise>
        </choose>
        IFNULL(SUM(promotion_num),0) AS 'new',
        '0' AS 'valid'
        FROM application.app_bytedance_account_base_info_d
        where stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
        GROUP BY
        statDate
        order by statDate asc
    </select>


    <select id="getPlanMessageBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id AS 'accountId',
        account_name AS 'authName',
        MAX(stat_date) AS 'maxCreateTime',
        '0' AS 'valid',
        IFNULL(SUM(promotion_num),0) AS 'new',
        user_name AS 'userName'
        FROM
        application.app_bytedance_account_base_info_d
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="array != null and array.size() > 0">
            and account_id in
            <foreach item="item" collection="array" separator="," open="(" close=")" index="">
                #{item}
            </foreach>
        </if>
        GROUP BY account_id
        order by `new` desc
    </select>
</mapper>
