DataCenterGame/example/update_cost_data.py

from model.DateUtils import DateUtils
from app.api_data.tx_ad_cost import get_cost_older,get_cost
from app.etl.data_stat_run import do_cost
from app.etl.dm.dm_pitcher_daily_overview import dm_pitcher_daily_overview
from app.etl.dw.dw_channel_daily import dw_channel_daily
from app.etl.dw.dw_pitcher_daily import dw_pitcher_trend
from app.etl.src.src_book_info import src_book_info
from app.etl.dw.dw_book_trend import book_trend
from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils
from app.api_data.tx_ad_cost.get_cost_older import get_v_data, mysql_insert_daily_vx, get_q_data, mysql_insert_daily_qq
import logging
import time
from logging import handlers

db = MysqlUtils()
du = DateUtils()


def get_data(st, et):
    # 1.获取数据

    print(st, et)
    get_cost_older.run(st, et)

    # 同步到ck
    do_cost(st, et)

    # 2.数据处理
    src_book_info()  # 书籍卡点信息
    # book_annual_expect_profit.run() # 年预期收益
    dw_channel_daily()
    dw_pitcher_trend()
    book_trend()
    dm_pitcher_daily_overview()

def get_data_vx_adinfo(channel, st, et):
    # 用于处理单个微信号相关信息
    sql = '''
            select account_id,wechat_account_id,access_token,refresh_token,name,
            ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_vx
            where account_id in (select account_id from channel_by_account_daily
            where channel ='{}'  and dt in (select max(dt) from channel_by_account_daily cbad)
            order by dt desc  )
                '''.format(channel)

    token_list_v = db.quchen_text.getData(sql)
    print(token_list_v)
    account_id=token_list_v[0][0]
    access_token=token_list_v[0][2]
    type='MP'

    #获取广告id
    get_cost.get_ad_cost_day(account_id, access_token, type, st, et)


    for dt in du.getDateLists(st,et):
        #获取广告基础信息
        sql = f"""SELECT b.account_id,b.access_token,b.type,GROUP_CONCAT(ad_id)  from ad_cost_day  a
            left join (
            select account_id,access_token,'MP' type  from advertiser_vx where (name !='' or name is not null) union 
            select account_id,access_token,'GDT' type  from advertiser_qq where (name !='' or name is not null)
            ) b on a.account_id=b.account_id
            where a.dt='{dt}' and a.account_id ='{account_id}'
            GROUP BY b.account_id,b.access_token,b.type"""

        accounts = db.quchen_text.getData(sql)

        for account in accounts:
            get_cost.get_ad_info(account[0], account[1], account[2], account[3], dt)

        #获取广告创意素材
        sql = f"""SELECT b.account_id,b.access_token,b.type,GROUP_CONCAT(adcreative_id)  from ad_info  a
                    left join (
                    select account_id,access_token,'MP' type  from advertiser_vx where  (name !='' or name is not null) union 
                    select account_id,access_token,'GDT' type from advertiser_qq where (name !='' or name is not null)
                    ) b on a.account_id=b.account_id
                    where a.dt='{dt}' and a.account_id='{account_id}'
                    GROUP BY b.account_id,b.access_token,b.type having b.account_id is not null """

        accounts = db.quchen_text.getData(sql)

        for account in accounts:
            get_cost.get_adcreatives(account[0], account[1], account[2], account[3], dt)

        #获取广告计划信息
        sql = f"""SELECT b.account_id,b.access_token,b.type,GROUP_CONCAT(campaign_id)  from ad_info  a
                    left join (
                    select account_id,access_token,'MP' type  from advertiser_vx where  (name !='' or name is not null) union 
                    select account_id,access_token,'GDT' type from advertiser_qq where  (name !='' or name is not null)
                    ) b on a.account_id=b.account_id
                    where a.dt='{dt}' and a.account_id='{account_id}'
                    GROUP BY b.account_id,b.access_token,b.type having b.account_id is not null """

        accounts = db.quchen_text.getData(sql)

        for account in accounts:
            get_cost.get_campaign(account[0], account[1], account[2], account[3], dt)

        #获取图片信息
        sql = f"""SELECT b.account_id,b.access_token,b.type,GROUP_CONCAT(image_id)  from adcreative_info  a
                    left join (
                    select account_id,access_token,'MP' type  from advertiser_vx where (name !='' or name is not null) union 
                    select account_id,access_token,'GDT' type from advertiser_qq where (name !='' or name is not null)
                    ) b on a.account_id=b.account_id
                    where a.dt='{dt}' and a.account_id='{account_id}' and a.is_video=0
                    GROUP BY b.account_id,b.access_token,b.type"""
        accounts = db.quchen_text.getData(sql)
        for account in accounts:
            get_cost.images_info_get( account[0], account[1], account[3])

        #获取视频信息
        sql = f"""SELECT b.account_id,b.access_token,b.type,GROUP_CONCAT(image_id)  from adcreative_info  a
                    left join (
                    select account_id,access_token,'MP' type  from advertiser_vx where (name !='' or name is not null) union 
                    select account_id,access_token,'GDT' type from advertiser_qq where (name !='' or name is not null)
                    ) b on a.account_id=b.account_id
                    where a.dt='{dt}' and a.account_id='{account_id}' and a.is_video=1
                    GROUP BY b.account_id,b.access_token,b.type"""
        accounts = db.quchen_text.getData(sql)
        for account in accounts:
            get_cost.video_info_get( account[0], account[1], account[3])



def get_data_vx(channel, st, et):
    # 用于处理单个微信号相关信息
    sql = '''
            select account_id,wechat_account_id,access_token,refresh_token,name,
            ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_vx
            where account_id in (select account_id from channel_by_account_daily
            where channel ='{}'  and dt in (select max(dt) from channel_by_account_daily cbad)
            order by dt desc  )
                '''.format(channel)

    token_list_v = db.quchen_text.getData(sql)
    print(token_list_v)
    time1 = time.time()
    li = []
    for y in token_list_v:
        get_v_data(y, li, st, et)
    for _ in li:
        print(_)
    print('get_daily_vx:' + str(len(li)) + 'cost:' + str(int(time.time() - time1)))
    mysql_insert_daily_vx(li)
    do_cost(st, et)
    src_book_info()  # 书籍卡点信息
    dw_channel_daily()
    dw_pitcher_trend()
    book_trend()
    dm_pitcher_daily_overview()


def get_data_gdt(channel, st, et):
    # 用于处理单个微信号相关信息
    sql = '''select account_id,'',access_token,refresh_token,name,
                ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_qq
                where account_id in (select account_id from channel_by_account_daily
                where channel ='{}' and dt in (select max(dt) from channel_by_account_daily cbad)
                order by dt desc  )
                '''.format(channel)
    print(sql)
    token_list_v = db.quchen_text.getData(sql)
    print(token_list_v)
    time1 = time.time()
    li = []
    for y in token_list_v:
        get_q_data(y, li, st, et)
    for _ in li:
        print(_)
    print('get_daily_qq:' + str(len(li)) + 'cost:' + str(int(time.time() - time1)))
    mysql_insert_daily_qq(li)
    do_cost(st, et)
    src_book_info()  # 书籍卡点信息
    dw_channel_daily()
    dw_pitcher_trend()
    book_trend()
    dm_pitcher_daily_overview()


if __name__ == "__main__":
    logging.basicConfig(
        handlers=[
            logging.handlers.RotatingFileHandler('./cost_data.log',
                                                 maxBytes=10 * 1024 * 1024,
                                                 backupCount=5,
                                                 encoding='utf-8')
            , logging.StreamHandler()  # 供输出使用
        ],
        level=logging.INFO,
        format="%(asctime)s - %(levelname)s %(filename)s %(funcName)s %(lineno)s - %(message)s"
    )
    st = du.get_n_days(-365)
    et = du.get_n_days(0)
    # get_data_vx(channel='丹青文阅', st=st, et=et)
    # get_data_gdt(channel='落枫文海', st=st, et=et)
    # get_data(st,et)
    # from app.etl import data_stat_task
    # data_stat_task.dw_daily_channel_cost('2019-07-12')
    get_data_vx_adinfo(channel='丹青文阅', st='2021-06-01', et='2021-06-05')