--- a/ckanext/dga_stats/stats.py +++ b/ckanext/dga_stats/stats.py @@ -2,9 +2,12 @@ from pylons import config from sqlalchemy import Table, select, func, and_ +from sqlalchemy.sql.expression import text import ckan.plugins as p import ckan.model as model + +import re cache_enabled = p.toolkit.asbool(config.get('ckanext.stats.cache_enabled', 'True')) @@ -42,7 +45,7 @@ package_revision = table('package_revision') package = table('package') s = select([package_revision.c.id, func.count(package_revision.c.revision_id)], from_obj=[package_revision.join(package)]).\ - where(package.c.private == 'f').\ + where(package.c.private == 'f').\ group_by(package_revision.c.id).\ order_by(func.count(package_revision.c.revision_id).desc()).\ limit(limit) @@ -52,53 +55,78 @@ @classmethod def largest_groups(cls, limit=10): - member = table('member') - package = table('package') - s = select([member.c.group_id, func.count(member.c.table_id)]).\ + member = table('member') + s = select([member.c.group_id, func.count(member.c.table_id)]).\ group_by(member.c.group_id).\ - where(and_(member.c.group_id!=None, member.c.table_name=='package', package.c.private == 'f')).\ - order_by(func.count(member.c.table_id).desc()).\ - limit(limit) - - res_ids = model.Session.execute(s).fetchall() - res_groups = [(model.Session.query(model.Group).get(unicode(group_id)), val) for group_id, val in res_ids] + where(member.c.group_id!=None).\ + where(member.c.table_name=='package').\ + where(member.c.capacity=='public').\ + order_by(func.count(member.c.table_id).desc()) + #limit(limit) + + res_ids = model.Session.execute(s).fetchall() + res_groups = [(model.Session.query(model.Group).get(unicode(group_id)), val) for group_id, val in res_ids] + return res_groups + + @classmethod + def by_org(cls, limit=10): + connection = model.Session.connection() + res = connection.execute("select package.owner_org, package.private, count(*) from package \ + inner join (select distinct package_id from resource_group inner join resource on resource.resource_group_id = resource_group.id) as r on package.id = r.package_id \ + inner join \"group\" on package.owner_org = \"group\".id \ + where package.state='active'\ + 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_tags(cls, limit=10, returned_tag_info='object'): # by package - assert returned_tag_info in ('name', 'id', 'object') - tag = table('tag') - package_tag = table('package_tag') - package = table('package') - #TODO filter out tags with state=deleted - if returned_tag_info == 'name': - from_obj = [package_tag.join(tag)] - tag_column = tag.c.name - else: - from_obj = None - tag_column = package_tag.c.tag_id - s = select([tag_column, func.count(package_tag.c.package_id)], - from_obj=from_obj) - s = s.group_by(tag_column).\ - where(package.c.private == 'f').\ - order_by(func.count(package_tag.c.package_id).desc()).\ - limit(limit) - res_col = model.Session.execute(s).fetchall() - if returned_tag_info in ('id', 'name'): - return res_col - elif returned_tag_info == 'object': - res_tags = [(model.Session.query(model.Tag).get(unicode(tag_id)), val) for tag_id, val in res_col] - return res_tags + def res_by_org(cls, limit=10): + connection = model.Session.connection() + reses = connection.execute("select owner_org,format,count(*) from \ + resource inner join resource_group on resource.resource_group_id = resource_group.id \ + inner join package on resource_group.package_id = package.id group by owner_org,format order by count desc;").fetchall(); + group_ids = [] + group_tab = {} + group_spatial = {} + group_other = {} + for group_id,format,count in reses: + if group_id not in group_ids: + group_ids.append(group_id) + group_tab[group_id] = 0 + group_spatial[group_id] = 0 + group_other[group_id] = 0 + if re.search('xls|csv|ms-excel|spreadsheetml.sheet|zip|netcdf',format, re.IGNORECASE): + group_tab[group_id] = group_tab[group_id] + count + elif re.search('wms|wfs|wcs|shp|kml|kmz',format, re.IGNORECASE): + group_spatial[group_id] = group_spatial[group_id] + count + else: + group_other[group_id] = group_other[group_id] + count + return [(model.Session.query(model.Group).get(unicode(group_id)), group_tab[group_id],group_spatial[group_id],group_other[group_id], group_tab[group_id]+group_spatial[group_id]+group_other[group_id]) for group_id in group_ids] + + @classmethod + def top_active_orgs(cls, limit=10): + connection = model.Session.connection() + res = connection.execute("select package.owner_org, count(*) from package \ + inner join (select distinct package_id from resource_group inner join resource on resource.resource_group_id = resource_group.id) as r on package.id = r.package_id \ + 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 def top_package_owners(cls, limit=10): package_role = table('package_role') user_object_role = table('user_object_role') package = table('package') -# join(package, package_role.c.package_id == package.c.id).\ -# where(package.c.private == 'f').\ - s = select([user_object_role.c.user_id, func.count(user_object_role.c.role)], from_obj=[user_object_role.join(package_role)]).\ + s = select([user_object_role.c.user_id, func.count(user_object_role.c.role)], from_obj=[user_object_role.join(package_role).join(package, package_role.c.package_id == package.c.id)]).\ where(user_object_role.c.role==model.authz.Role.ADMIN).\ + where(package.c.private == 'f').\ where(user_object_role.c.user_id!=None).\ group_by(user_object_role.c.user_id).\ order_by(func.count(user_object_role.c.role).desc()).\ @@ -110,11 +138,12 @@ @classmethod def summary_stats(cls): connection = model.Session.connection() -# select name,role from user_object_role inner join \"user\" on user_object_role.user_id = \"user\".id where name not in ('logged_in','visitor') group by name,role" 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' union \ - select 'Total Data Files/Resources', count(*) from resource where state='active'").fetchall(); + select 'Total Datasets', count(*) from package inner join (select distinct package_id from resource_group inner join resource on resource.resource_group_id = resource_group.id) as r on package.id = r.package_id where (package.state='active' or package.state='draft' or package.state='draft-complete') and private = 'f' union \ + 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' or format='wms')").fetchall(); return res @@ -123,6 +152,22 @@ connection = model.Session.connection() res = connection.execute("select to_char(timestamp, 'YYYY-MM') as month,activity_type, count(*) from activity group by month, activity_type order by month;").fetchall(); return res + + @classmethod + def user_access_list(cls): + connection = model.Session.connection() + res = connection.execute("select name,sysadmin,role from user_object_role right outer join \"user\" on user_object_role.user_id = \"user\".id where name not in ('logged_in','visitor') group by name,sysadmin,role order by sysadmin desc, role asc;").fetchall(); + return res + + @classmethod + def recent_datasets(cls): + activity = table('activity') + package = table('package') + s = select([func.max(activity.c.timestamp),package.c.id, activity.c.activity_type], from_obj=[activity.join(package,activity.c.object_id == package.c.id)]).where(package.c.private == 'f').\ + where(activity.c.timestamp > func.now() - text("interval '60 day'")).group_by(package.c.id,activity.c.activity_type).order_by(func.max(activity.c.timestamp)) + result = model.Session.execute(s).fetchall() + return [(datetime2date(timestamp), model.Session.query(model.Package).get(unicode(package_id)), activity_type) for timestamp,package_id,activity_type in result] + class RevisionStats(object): @@ -193,7 +238,8 @@ # be 'for all time' else you get first revision in the time period. package_revision = table('package_revision') revision = table('revision') - s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision)]).\ + package = table('package') + s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision).join(package)]).\ where(package.c.private == 'f').\ group_by(package_revision.c.id).order_by(func.min(revision.c.timestamp)) res = model.Session.execute(s).fetchall() # [(id, datetime), ...] @@ -221,8 +267,10 @@ # be 'for all time' else you get first revision in the time period. package_revision = table('package_revision') revision = table('revision') - s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision)]).\ + package = table('package') + s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision).join(package)]).\ where(package_revision.c.state==model.State.DELETED).\ + where(package.c.private == 'f').\ group_by(package_revision.c.id).\ order_by(func.min(revision.c.timestamp)) res = model.Session.execute(s).fetchall() # [(id, datetime), ...]