def retrieve_campaign_rollups():
return run_safe_dict_query(
connections['reporting'].cursor(),
"""
SELECT
campaignrollups.campaign_id as root_id,
campaigns.name,
max(latest_activity) as latest_activity,
min(first_activity) as first_activity,
{}
FROM campaignrollups
JOIN campaigns using (campaign_id)
JOIN (
select
campaign_id,
to_char(max(hour), 'YYYY-MM-DD') as latest_activity,
to_char(min(hour), 'YYYY-MM-DD') as first_activity
from campaignhourly group by campaign_id
) as timelookup using (campaign_id)
WHERE campaigns.client_id = %s
GROUP BY campaignrollups.campaign_id, campaigns.name
ORDER BY campaignrollups.campaign_id desc
""".format(metric_where_fragment()),
(client.client_id,)
)
def retrieve_client_rollups():
return run_safe_dict_query(
connections['reporting'].cursor(),
"""
SELECT
{}
FROM clientrollups
WHERE client_id = %s
""".format(metric_where_fragment()),
(client.client_id,)
)
def campaign_hourly(request, client_pk, campaign_pk):
""" Data for a particular campaign, used by the chart view """
query = """
SELECT
date_trunc('hour', hour) as time,
{}
FROM campaignhourly
JOIN campaigns using (campaign_id)
WHERE campaigns.campaign_id = %s
GROUP BY time
ORDER BY time ASC
""".format(metric_where_fragment())
cursor = connections['reporting'].cursor()
response_format = request.GET.get('format', '')
def retrieve_campaign_hourly_csv():
def csv_tz(row, indices):
for index in indices:
aware_ts = row[index] if timezone.is_aware(row[index]) else timezone.make_aware(row[index], timezone.utc)
row[index] = aware_ts.strftime('%Y-%m-%dT%H:%M:%S %Z')
return row
data = run_safe_row_query(
cursor,
query,
(campaign_pk,)
)
data = [csv_tz(row, [0]) for row in data]
data.insert(0, [col.name for col in cursor.description])
return data
def retrieve_campaign_hourly():
return run_safe_dict_query(
cursor,
query,
(campaign_pk,)
)
if response_format == 'csv':
data = cached_report(
'campaignhourlycsv',
campaign_pk,
retrieve_campaign_hourly_csv
)
return generate_csv(data, client_pk)
else:
data = cached_report(
'campaignhourly',
campaign_pk,
retrieve_campaign_hourly
)
return JsonResponse({'data': [isoformat_dict(row) for row in data]})