--- a/ckanext/dga_stats/stats.py +++ b/ckanext/dga_stats/stats.py @@ -75,6 +75,20 @@ group by package.owner_org,\"group\".name, package.private \ order by \"group\".name, package.private;").fetchall(); res_groups = [(model.Session.query(model.Group).get(unicode(group_id)), private, val) for group_id, private, val in res] + return res_groups + + @classmethod + def top_active_orgs(cls, limit=10): + connection = model.Session.connection() + res = connection.execute("select package.owner_org, count(*) from package \ + inner join \"group\" on package.owner_org = \"group\".id \ + inner join (select distinct object_id from activity where activity.timestamp > (now() - interval '60 day')) \ + latestactivities on latestactivities.object_id = package.id \ + where package.state='active' \ + and package.private = 'f' \ + group by package.owner_org \ + order by count(*) desc;").fetchall(); + res_groups = [(model.Session.query(model.Group).get(unicode(group_id)), val) for group_id, val in res] return res_groups @classmethod @@ -123,10 +137,12 @@ def summary_stats(cls): connection = model.Session.connection() -# select 'Total Archived Datasets', count(*) from package where (state='active' or state='draft' or state='draft-complete') and private = 't' union \ res = connection.execute("SELECT 'Total Organisations', count(*) from \"group\" where type = 'organization' and state = 'active' union \ select 'Total Datasets', count(*) from package where (state='active' or state='draft' or state='draft-complete') and private = 'f' union \ - select 'Total Data Files/Resources', count(*) from resource where state='active'").fetchall(); + select 'Total Archived Datasets', count(*) from package where (state='active' or state='draft' or state='draft-complete') and private = 't' union \ + select 'Total Data Files/Resources', count(*) from resource where state='active' union \ + select 'Total Machine Readable/Data API Resources', count(*) from resource where state='active' and webstore_url = 'active'\ + ").fetchall(); return res