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')