1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | import logging import operator from ckan.lib.base import BaseController, c, render, request, response import sqlalchemy from sqlalchemy import func, cast, Integer import ckan.model as model from ga_model import GA_Url, GA_Stat log = logging.getLogger('ckanext.ga-report') def _get_month_name(strdate): import calendar from time import strptime d = strptime(strdate, '%Y-%m') return '%s %s' % (calendar.month_name[d.tm_mon], d.tm_year) def _month_details(cls): months = [] vals = model.Session.query(cls.period_name).distinct().all() for m in vals: months.append( (m[0], _get_month_name(m[0]))) return sorted(months, key=operator.itemgetter(0), reverse=True) class GaReport(BaseController): def csv(self, month): import csv entries = model.Session.query(GA_Stat).\ filter(GA_Stat.period_name==month).\ order_by('GA_Stat.stat_name, GA_Stat.key').all() response.headers['Content-disposition'] = 'attachment; filename=dgu_analytics_%s.csv' % (month) response.headers['Content-Type'] = "text/csv; charset=utf-8" writer = csv.writer(response) writer.writerow(["Period", "Statistic", "Key", "Value"]) for entry in entries: writer.writerow([entry.period_name.encode('utf-8'), entry.stat_name.encode('utf-8'), entry.key.encode('utf-8'), entry.value.encode('utf-8')]) def index(self): # Get the month details by fetching distinct values and determining the # month names from the values. c.months = _month_details(GA_Stat) # Work out which month to show, based on query params of the first item c.month = request.params.get('month', c.months[0][0] if c.months else '') c.month_desc = ''.join([m[1] for m in c.months if m[0]==c.month]) entries = model.Session.query(GA_Stat).\ filter(GA_Stat.stat_name=='Totals').\ filter(GA_Stat.period_name==c.month).\ order_by('ga_stat.key').all() c.global_totals = [(s.key, s.value) for s in entries ] keys = { 'Browser versions': 'browsers', 'Operating Systems versions': 'os', 'Social sources': 'social_networks', 'Languages': 'languages', 'Country': 'country' } for k, v in keys.iteritems(): entries = model.Session.query(GA_Stat).\ filter(GA_Stat.stat_name==k).\ filter(GA_Stat.period_name==c.month).\ order_by('ga_stat.value::int desc').all() setattr(c, v, [(s.key, s.value) for s in entries ]) return render('ga_report/site/index.html') class GaPublisherReport(BaseController): """ Displays the pageview and visit count for specific publishers based on the datasets associated with the publisher. """ def index(self): try: # Get the month details by fetching distinct values and determining the # month names from the values. c.months = _month_details(GA_Url) # Work out which month to show, based on query params of the first item c.month = request.params.get('month', c.months[0][0] if c.months else '') c.month_desc = ''.join([m[1] for m in c.months if m[0]==c.month]) connection = model.Session.connection() q = """ select department_id, sum(pageviews::int) views, sum(visitors::int) visits from ga_url where department_id <> '' and not url like '/publisher/%%' and period_name=%s group by department_id order by views desc limit 20; """ c.top_publishers = [] res = connection.execute(q, c.month) for row in res: c.top_publishers.append((model.Group.get(row[0]), row[1], row[2])) except Exception as e: raise e return render('ga_report/publisher/index.html') def read(self, id): c.publisher = model.Group.get(id) c.top_packages = [] # package, dataset_views in c.top_packages # Get the month details by fetching distinct values and determining the # month names from the values. c.months = _month_details(GA_Url) # Work out which month to show, based on query params of the first item c.month = request.params.get('month', c.months[0][0] if c.months else '') c.month_desc = ''.join([m[1] for m in c.months if m[0]==c.month]) entry = model.Session.query(GA_Url).\ filter(GA_Url.url=='/publisher/%s' % c.publisher.name).\ filter(GA_Url.period_name==c.month).first() c.publisher_page_views = entry.pageviews if entry else 0 entries = model.Session.query(GA_Url).\ filter(GA_Url.department_id==c.publisher.name).\ filter(GA_Url.period_name==c.month).\ order_by('ga_url.pageviews::int desc')[:20] for entry in entries: if entry.url.startswith('/dataset/'): p = model.Package.get(entry.url[len('/dataset/'):]) c.top_packages.append((p,entry.pageviews,entry.visitors)) return render('ga_report/publisher/read.html') |