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