def get_advertiser_stats(user, from_date, to_date):
campaigns_list = Campaign.objects.filter(user=user)
campaign_id_list = [str(site.id) for site in campaigns_list]
campaign_id_list_str = ",".join(campaign_id_list)
date_diff = (to_date-from_date)/(3600*24)
query = "select dt.date, pb.campaign_id as campaign_id, count(bss) as shows, count(bc) as clicks from \
(select timestamp(%d)::date - s.a AS date from Generate_series(%d, 0, -1) AS s(a)) as dt \
inner join \
platform_banner as pb \
on 1=1 \
left join \
banner_show_stats as bss \
on bss.banner_id = pb.id and date(bss.created)=dt.date \
left join \
banner_clicks as bc \
on bc.banner_id = pb.id and date(bss.created)=dt.date \
where pb.campaign_id in (%s) \
group by pb.campaign_id,dt.date;" % (to_date, date_diff, campaign_id_list_str)
data = get_data_from_sql(query)
for entry in data:
for campaign in campaigns_list:
if campaign.id == entry['campaign_id']:
entry["date"] = str(entry["date"])
entry['campaign_name'] = campaign.name
break
return data
def get_publisher_stats(user, from_date, to_date):
sites_list = UserSite.objects.filter(user=user)
site_id_list = [str(site.id) for site in sites_list]
site_id_list_str = ",".join(site_id_list)
date_diff = (to_date-from_date)/(3600*24)
query = "select dt.date, pa.site_id as site_id, pa.id as spot_id, count(bss) as shows, count(bc) as clicks from\
(select to_timestamp(%d)::date - s.a AS date from Generate_series(%d, 0, -1) AS s(a)) as dt\
inner join \
platform_adspot as pa \
on 1=1 \
left join \
banner_show_stats as bss \
on bss.spot_code = pa.unique_code and date(bss.created)=dt.date \
left join \
banner_clicks as bc \
on bc.spot_code = pa.unique_code and date(bss.created)=dt.date \
where pa.site_id in (%s) \
group by pa.id,dt.date;" % (to_date, date_diff, site_id_list_str)
data = get_data_from_sql(query)
for entry in data:
for site in sites_list:
if site.id == entry['site_id']:
entry["date"] = str(entry["date"])
entry['site_name'] = site.name
break
return data
def get_site_stats(site_id_list):
site_id_string = ','.join(site_id_list)
query = 'select pus.id, CURRENT_DATE - s.a AS date, count(DISTINCT bss.id), count(DISTINCT bcc.id) \
from platform_usersite as pus \
inner join \
Generate_series(0, 30, 1) AS s(a) \
On true \
LEFT JOIN platform_adspot as pa \
ON pa.site_id=pus.id \
LEFT JOIN banner_show_stats as bss \
ON date(bss.created)=CURRENT_DATE - s.a and bss.spot_code=pa.unique_code \
LEFT JOIN banner_clicks as bcc \
On date(bcc.created)=CURRENT_DATE - s.a AND bcc.spot_code=pa.unique_code \
WHERE pus.id in (%s) \
GROUP BY pus.id, CURRENT_DATE - s.a' % site_id_string
data = get_data_from_sql(query)
return data
