def _clean_data(config, dict_data, table_name):
batch_ids = list(set([d['batch_id'] for d in dict_data]))
query = '''DELETE FROM %s WHERE batch_id in ('%s')''' % (
table_name, "','".join(batch_ids))
try:
execute_query(config=config, query=query)
except Exception as e:
logging.info(str(e))
def _update_report_status(report, config):
table_name = '%s.%s' % (config.get('schema_name'), 'report_async')
updated_at = str(datetime.datetime.now())[:19]
# noinspection SqlNoDataSourceInspection
query = '''
UPDATE %s
SET updated_at = '%s' ,
status = '%s',
result_fetch = '%s'
WHERE report_run_id = '%s'
''' % (table_name, updated_at, report["status"], report["result_fetch"],
report["report_run_id"])
execute_query(query, config)
def get(config, id):
schema_name = config.get('schema_name')
r = execute_query('SELECT * FROM %s.app_system_user WHERE id=%s' % (schema_name, id), config)
if not r:
print('SystemUser does not exist')
exit(1)
return SystemUser(**r[0])
def define_start_date(config, report, time_increment, account, async):
if async:
table_name = '%s.%s' % (config.get('schema_name'), 'report_async')
query = """
SELECT max(end_report) as start_date
FROM %s
WHERE account_id='%s' and time_increment='%s' and report_name='%s' and status='Job Completed'
and created_at>=end_report
""" % (table_name, account["id"], time_increment,
report.get('name'))
else:
table_name = '%s.%s_%s' % (config.get('schema_name'),
report.get('name'), time_increment)
query = """
SELECT max(date_start) as start_date
FROM %s
WHERE account_id='%s'
""" % (table_name, account["account_id"])
start_date = execute_query(query, config)[0]["start_date"]
if start_date:
return str(
datetime.datetime.strptime(str(start_date)[:10], '%Y-%m-%d') -
datetime.timedelta(days=28))[:10]
else:
return None
def check_and_fetch_reports(config):
table_name = '%s.%s' % (config.get('schema_name'), 'report_async')
all_status = ['Job Completed', 'Job Failed', 'Job Skipped']
query = '''SELECT * FROM %s WHERE status not in ('%s') or status is NULL or result_fetch is NULL''' % (
table_name, "','".join(all_status))
all_reports = execute_query(config=config, query=query)
job_not_completed_yet = False
for r in all_reports:
if r["status"] != 'Job Completed':
result = _get_report_status(system_user=SystemUser.get(
config, r["app_system_user_id"]),
endpoint=r['report_run_id'],
params={})
status = result['async_status']
else:
status = r["status"]
if status == 'Job Completed':
_send_data_fetch(_fetch_report(config, r),
time_increment=r["time_increment"],
report_name=r["report_name"],
config=config)
r["result_fetch"] = str(datetime.datetime.now())[:19]
else:
job_not_completed_yet = True
r["result_fetch"] = None
r["status"] = status
_update_report_status(r, config)
return job_not_completed_yet
def get_all_campaigns(config_path, history=False):
config = get_config(config_path)
query = 'SELECT DISTINCT id, app_system_user_id FROM %s' % (
config["schema_name"] + '.ad_accounts')
accounts = execute_query(config=config, query=query)
data = []
table_name = "%s.campaigns" % config.get("schema_name")
for account in accounts:
data = data + get_campaigns(account, config, history)
process_campaign(data, table_name, config)
def define_updated_time_filter(config, report, time_increment, account):
table_name = '%s.%s_%s' % (config.get('schema_name'), report.get('name'),
time_increment)
query = """
SELECT max(updated_time) as updated_time
FROM %s
WHERE account_id='%s'
""" % (table_name, account["account_id"])
try:
updated_time = execute_query(query, config)[0]["updated_time"]
except Exception as e:
logging.info(str(e))
return None
if updated_time:
return datetime.datetime.strptime(
str(updated_time)[:10], '%Y-%m-%d') - datetime.timedelta(days=28)
else:
return None
def fetch_running_job(config, report_run_id):
table_name = '%s.%s' % (config.get('schema_name'), 'report_async')
query = '''SELECT * FROM %s WHERE report_run_id = '%s' ''' % (
table_name, report_run_id)
r = execute_query(config=config, query=query)[0]
result = _get_report_status(system_user=SystemUser.get(
config, r["app_system_user_id"]),
endpoint=r['report_run_id'],
params={})
status = result['async_status']
if status == 'Job Completed':
_send_data_fetch(_fetch_report(config, r),
time_increment=r["time_increment"],
report_name=r["report_name"],
config=config)
r["result_fetch"] = str(datetime.datetime.now())[:19]
else:
logging.info('Job not completed yet')
exit()
r["status"] = status
_update_report_status(r, config)
def prepare_report_request(config, report):
result = dict()
result["level"] = report["level"]
fields = report["fields"].copy()
if report.get("filtering"):
result["filtering"] = report.get("filtering")
if report.get("action_attribution_windows"):
result["action_attribution_windows"] = report.get("action_attribution_windows")
if report.get("action_report_time"):
result["action_report_time"] = report.get("action_report_time")
if 'account_id' not in fields:
fields.append('account_id')
if "purchase" in fields:
fields[fields.index("purchase")] = "actions"
elif "total_actions" in fields:
fields[fields.index("total_actions")] = "actions"
if "video_view_10_sec" in fields:
fields[fields.index("video_view_10_sec")] = "video_10_sec_watched_actions"
if "updated_time" not in fields:
fields.append("updated_time")
fields = ", ".join(fields)
result["fields"] = fields
if report.get("breakdowns"):
breakdowns = [b for b in report["breakdowns"]]
breakdowns = ", ".join(breakdowns)
else:
breakdowns = None
result["breakdowns"] = breakdowns
if report.get('ad_accounts'):
accounts = report.get('ad_accounts')
query = "SELECT DISTINCT id, app_system_user_id, account_id FROM %s WHERE id in ('%s')" % (
config["schema_name"] + '.ad_accounts', "','".join(accounts))
else:
query = 'SELECT DISTINCT id, app_system_user_id, account_id FROM %s' % (config["schema_name"] + '.ad_accounts')
accounts = execute_query(config=config, query=query)
result["accounts"] = accounts
return result
def all(config):
schema_name = config.get('schema_name')
r = execute_query('SELECT * FROM %s.app_system_user' % schema_name, config)
return [SystemUser(**result) for result in r]
def _clean(all_ids, table_name, config):
query = 'DELETE FROM %s WHERE id in (%s)' % (table_name, ",".join(all_ids))
try:
execute_query(config=config, query=query)
except Exception as e:
logging.info(str(e))
