<?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.common.module.mapper.BytedanceProjectMapper">


    <insert id="replaceProjectList">
        REPLACE INTO bytedance_cdp_project (
        project_id,
        `name`,
        advertiser_id,
        status,
        package_name,
        project_create_time,
        project_modify_time,
        pricing,
        landing_type,
        ad_type,
        audience_extend,
        opt_status,
        app_promotion_type,
        delivery_mode,
        search_bid_ratio,
        download_url,
        download_mode,
        download_type,
        app_name,
        feed_delivery_search,
        marketing_goal,
        keywords,
        delivery_setting,
        dpa_product_target,
        optimize_goal,
        audience,
        related_product,
        delivery_range,
        track_url_setting,
        internal_advertiser_info
        )
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.projectId},
            #{item.name},
            #{item.advertiserId},
            #{item.status},
            #{item.packageName},
            #{item.projectCreateTime},
            #{item.projectModifyTime},
            #{item.pricing},
            #{item.landingType},
            #{item.adType},
            #{item.audienceExtend},
            #{item.optStatus},
            #{item.appPromotionType},
            #{item.deliveryMode},
            #{item.searchBidRatio},
            #{item.downloadUrl},
            #{item.downloadMode},
            #{item.downloadType},
            #{item.appName},
            #{item.feedDeliverySearch},
            #{item.marketingGoal},
            #{item.keywords},
            #{item.deliverySetting},
            #{item.dpaProductTarget},
            #{item.optimizeGoal},
            #{item.audience},
            #{item.relatedProduct},
            #{item.deliveryRange},
            #{item.trackUrlSetting},
            #{item.internalAdvertiserInfo}
            )
        </foreach>
    </insert>

    <select id="getProjectId" resultType="Long">
        SELECT project_id
        FROM `jeecg-boot`.ctop_user_allocation
        WHERE account_id = #{accountId}
    </select>

    <!-- 获取用户微信id -->
    <select id="getWChatIdsByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select wexin_id, corp_id, wexin_name
        from `jeecg-boot`.ctop_corp_wexin_user
        where user_id = (select ctop_user_allocation.user_id
                         from `jeecg-boot`.ctop_user_allocation
                         where account_id = #{accountId} limit 1)
        union all
        select wexin_id, corp_id, wexin_name
        from `jeecg-boot`.ctop_corp_wexin_user
        where user_id = (select leader_id
                         from `jeecg-boot`.sys_user
                         where id = (SELECT ctop_user_allocation.user_id
                                     FROM `jeecg-boot`.ctop_user_allocation
                                     WHERE account_id = #{accountId}
            LIMIT 1))
        union all
        select wexin_id, corp_id, wexin_name
        from `jeecg-boot`.ctop_corp_wexin_user
        where user_id = 'b07f2bdd0f914f1dab2b4e841764911f';
    </select>

    <insert id="insertSendRecord">
        insert
        into `jeecg-boot`.ctop_media_send_alarm_record
        (account_id,
         plan_id,
         project_id,
         user_id,
         alarm_detail,
         media_type)
        values (#{accountId},
                #{planId},
                #{projectId},
                #{userId},
                #{alarmDetail},
                #{mediaType})
    </insert>

    <insert id="insertSendAccountId">
        replace
        into `jeecg-boot`.bytedance_refusal_send
        (account_id,ad_id,
         counts,
         stat_date)
        values (
        #{accountId},
        #{adId},
        #{counts},
        (DATE_FORMAT(now(),'%Y%m%d-%H'))
        )
    </insert>
    <insert id="addLog">
        INSERT INTO `jeecg-boot`.ctop_bytedance_project_stop_log (account_id, project_id, project_status)
        VALUES (#{accountId}, #{projectId}, #{projectStatus});
    </insert>


    <select id="getBytedanceRefusalSend" resultType="Long">
        SELECT ad_id
        FROM `jeecg-boot`.bytedance_refusal_send
        WHERE stat_date = DATE_FORMAT(now(), '%Y%m%d-%H')
          AND account_id =#{accountId}
          AND counts >= 2
    </select>

    <select id="getSpecialProject" resultType="Long">
        SELECT IFNULL(project_id,-1)
        FROM `jeecg-boot`.bytedance_refusal_send
        WHERE flag = 1
        UNION
        SELECT IFNULL(account_id,-1)
        FROM `jeecg-boot`.bytedance_refusal_send
        WHERE flag = 1
    </select>

    <select id="getrefusalAccountCounts" resultType="Integer">
        SELECT ifNUll(counts, 0)
        FROM `jeecg-boot`.bytedance_refusal_send
        WHERE account_id = #{accountId}
          AND ad_id = #{adId}
          AND stat_date = DATE_FORMAT(now(), '%Y%m%d-%H')
    </select>
    <select id="getProjectByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select id 'projectId', project_name 'projectName', max_bid 'maxBid', bid_type 'bidType', ocpx_action_type 'ocpxActionType', deep_conversion_type 'deepConversionType'
        from `jeecg-boot`.ctop_project
        where id = (
            select project_id
            from `jeecg-boot`.ctop_user_allocation
            where account_id = #{accountId}
            limit 1
            )
    </select>
    <select id="getBytedanceOcpxTypeList" resultType="com.alibaba.fastjson.JSONObject">
        select ocpx_action_type as 'ocpxActionType', ocpx_action_name as 'ocpxActionName'
        from `jeecg-boot`.ctop_bytedance_ocpx_action_type_config
    </select>
    <select id="getTokenByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select t1.access_token as 'accessToken', t2.account_status as 'accountStatus'
        from `jeecg-boot`.ctop_oauth_token t1
                 left join `jeecg-boot`.ctop_user_allocation t2 on t1.account_id = t2.account_id
        where t1.account_id = #{advertiserId} limit 1

    </select>
    <select id="getLeaderByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select t.*
        from (select wexin_id, corp_id, wexin_name
              from `jeecg-boot`.ctop_corp_wexin_user
              where user_id = (select ctop_user_allocation.user_id
                               from `jeecg-boot`.ctop_user_allocation
                               where account_id =  #{accountId})
              union all
              select wexin_id, corp_id, wexin_name
              from `jeecg-boot`.ctop_corp_wexin_user
              where user_id = (select leader_id
                               from `jeecg-boot`.sys_user
                               where id = (SELECT ctop_user_allocation.user_id
                                           FROM `jeecg-boot`.ctop_user_allocation
                                           WHERE account_id =  #{accountId}
                  LIMIT 1))) t
        group by t.wexin_id
    </select>
    <select id="getAccountInfo" resultType="com.alibaba.fastjson.JSONObject">
        select t1.user_name as 'userName', t2.project_name as 'projectName'
        from `jeecg-boot`.ctop_user_allocation t1
                 left join `jeecg-boot`.ctop_project t2
                           on t1.project_id = t2.id
        where t1.account_id = #{accountId}
          and t1.account_status = 0
        limit 1

    </select>


</mapper>