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]})
def client_summary(request, client_pk):
"""
Data for the initial pageview, a summary of client stats grouped by campaign
"""
client = Client.objects.get(pk=client_pk)
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 retrieve_client_lastdata():
return Campaignhourly.objects \
.filter(campaign__client_id=client.client_id) \
.aggregate(max_hour=Max('hour'))['max_hour']
data = cached_report(
'campaignrollups',
client.client_id,
retrieve_campaign_rollups
)
rollup_data = cached_report(
'clientrollups',
client.client_id,
retrieve_client_rollups
)
last_updated = cached_report(
'clientlastdata',
client.client_id,
retrieve_client_lastdata
)
return JsonResponse({
'data': data,
'rollups': rollup_data,
'lastupdated': last_updated
})
