Collections functions
Encryption functions
General functions
Render functions
Theme permission functions
Video functions
Resource functions

Table: report

Reports as listed in the admin area.

ColumnTypeNote
refint(11)Auto incrementing index
namevarchar(100)
querytextSQL defining the report.

Default contents are as follows.

refnamequery
1Keywords used in resource editsselect k.keyword 'Keyword',sum(count) 'Entered Count' from keyword k,daily_stat d where k.ref=d.object_ref and d.activity_type='Keyword added to resource' # --- date ranges # Make sure date is greater than FROM date and ( d.year>[from-y] or (d.year=[from-y] and d.month>[from-m]) or (d.year=[from-y] and d.month=[from-m] and d.day>=[from-d]) ) # Make sure date is less than TO date and ( d.year<[to-y] or (d.year=[to-y] and d.month<[to-m]) or (d.year=[to-y] and d.month=[to-m] and d.day<=[to-d]) ) group by k.ref order by 'Entered Count' desc limit 100;
2Keywords used in searchesselect k.keyword 'Keyword',sum(count) Searches from keyword k,daily_stat d where k.ref=d.object_ref and d.activity_type='Keyword usage' # --- date ranges # Make sure date is greater than FROM date and ( d.year>[from-y] or (d.year=[from-y] and d.month>[from-m]) or (d.year=[from-y] and d.month=[from-m] and d.day>=[from-d]) ) # Make sure date is less than TO date and ( d.year<[to-y] or (d.year=[to-y] and d.month<[to-m]) or (d.year=[to-y] and d.month=[to-m] and d.day<=[to-d]) ) group by k.ref order by Searches desc
3Resource download summary select r.ref 'Resource ID', (SELECT `value` FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1) AS 'Title', count(*) Downloads from resource_log rl join resource r on rl.resource=r.ref where rl.type='d' and rl.date>=date('[from-y]-[from-m]-[from-d]') and rl.date<=adddate(date('[to-y]-[to-m]-[to-d]'),1) group by r.ref order by 'Downloads' desc
4Resource views select r.ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Title',sum(count) Views from resource r,daily_stat d where r.ref=d.object_ref and d.activity_type='Resource view' # --- date ranges # Make sure date is greater than FROM date and ( d.year>[from-y] or (d.year=[from-y] and d.month>[from-m]) or (d.year=[from-y] and d.month=[from-m] and d.day>=[from-d]) ) # Make sure date is less than TO date and ( d.year<[to-y] or (d.year=[to-y] and d.month<[to-m]) or (d.year=[to-y] and d.month=[to-m] and d.day<=[to-d]) ) group by r.ref order by Views desc;
5Resources sent via e-mail select r.ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Title',sum(count) Sent from resource r,daily_stat d where r.ref=d.object_ref and d.activity_type='E-mailed resource' # --- date ranges # Make sure date is greater than FROM date and ( d.year>[from-y] or (d.year=[from-y] and d.month>[from-m]) or (d.year=[from-y] and d.month=[from-m] and d.day>=[from-d]) ) # Make sure date is less than TO date and ( d.year<[to-y] or (d.year=[to-y] and d.month<[to-m]) or (d.year=[to-y] and d.month=[to-m] and d.day<=[to-d]) ) group by r.ref order by Sent desc;
6Resources added to collection select r.ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Title',sum(count) Added from resource r,daily_stat d where r.ref=d.object_ref and d.activity_type='Add resource to collection' # --- date ranges # Make sure date is greater than FROM date and ( d.year>[from-y] or (d.year=[from-y] and d.month>[from-m]) or (d.year=[from-y] and d.month=[from-m] and d.day>=[from-d]) ) # Make sure date is less than TO date and ( d.year<[to-y] or (d.year=[to-y] and d.month<[to-m]) or (d.year=[to-y] and d.month=[to-m] and d.day<=[to-d]) ) group by r.ref order by Added desc;
7Resources created select rl.date 'Date / Time', concat(u.username,' (',u.fullname,' )') 'Created By User', g.name 'User Group', r.ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Resource Title' from resource_log rl join resource r on r.ref=rl.resource left outer join user u on rl.user=u.ref left outer join usergroup g on u.usergroup=g.ref where rl.type='c' and rl.date>=date('[from-y]-[from-m]-[from-d]') and rl.date<=adddate(date('[to-y]-[to-m]-[to-d]'),1) order by rl.date
8Resources with zero downloads select ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Title' from resource where ref > 0 and ref not in ( select object_ref from daily_stat WHERE activity_type='Resource download' AND ( year>[from-y] or (year=[from-y] and month>[from-m]) or (year=[from-y] and month=[from-m] and day>=[from-d]) ) AND ( year<[to-y] or (year=[to-y] and month<[to-m]) or (year=[to-y] and month=[to-m] and day<=[to-d]) ) group by object_ref ) AND DATE_FORMAT(creation_date, '%Y-%m-%d')<=date('[to-y]-[to-m]-[to-d]') AND ref>0
9Resources with zero views select ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Title' from resource where ref not in ( SELECT object_ref FROM daily_stat d WHERE d.activity_type='Resource view' AND ( year>[from-y] or (year=[from-y] and month>[from-m]) or (year=[from-y] and month=[from-m] and day>=[from-d]) ) AND ( year<[to-y] or (year=[to-y] and month<[to-m]) or (year=[to-y] and month=[to-m] and day<=[to-d]) ) group by object_ref ) AND DATE_FORMAT(creation_date, '%Y-%m-%d')<=date('[to-y]-[to-m]-[to-d]') AND ref>0
10Resource downloads by group select g.name 'Group Name', count(rl.resource) 'Resource Downloads' from resource_log rl left outer join user u on rl.user=u.ref left outer join usergroup g on u.usergroup=g.ref where rl.type='d' and rl.date>=date('[from-y]-[from-m]-[from-d]') and rl.date<=adddate(date('[to-y]-[to-m]-[to-d]'),1) group by g.ref order by 'Resource Downloads' desc
11Resource download detail select rl.date 'Date / Time', concat(u.username,' (',u.fullname,' )') 'Downloaded By User', g.name 'User Group', r.ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Resource Title', rt.name 'Resource Type' from resource_log rl join resource r on r.ref=rl.resource left outer join user u on rl.user=u.ref left outer join usergroup g on u.usergroup=g.ref left outer join resource_type rt on r.resource_type=rt.ref where rl.type='d' and rl.date>=date('[from-y]-[from-m]-[from-d]') and rl.date<=adddate(date('[to-y]-[to-m]-[to-d]'),1) order by rl.date
12User details including group allocationselect u.username 'Username', u.email 'E-mail address', u.fullname 'Full Name', u.created 'Created', u.last_active 'Last Seen', g.name 'Group name' from user u join usergroup g on u.usergroup=g.ref order by username;
13Expired Resources select distinct resource.ref 'Resource ID',resource.field8 'Resource Title',resource_data.value 'Expires' from resource join resource_data on resource.ref=resource_data.resource join resource_type_field on resource_data.resource_type_field=resource_type_field.ref where resource_type_field.type=6 and value>=date('[from-y]-[from-m]-[from-d]') and value<=adddate(date('[to-y]-[to-m]-[to-d]'),1) and length(value)>0 and resource.ref>0 order by resource.ref;
14Resources created - with thumbnails select r.ref 'thumbnail', rl.date 'Date / Time', concat(u.username,' (',u.fullname,' )') 'Created By User', g.name 'User Group', r.ref 'Resource ID', ( SELECT rd.value FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) 'Resource Title' from resource_log rl join resource r on r.ref=rl.resource left outer join user u on rl.user=u.ref left outer join usergroup g on u.usergroup=g.ref where rl.type='c' and rl.date>=date('[from-y]-[from-m]-[from-d]') and rl.date<=adddate(date('[to-y]-[to-m]-[to-d]'),1) order by rl.date;
16Database statistics select (select count(*) from resource) as 'Total resources', (select count(*) from keyword) 'Total keywords', (select count(*) from resource_keyword) as 'Resource keyword relationships', (select count(*) from collection) as 'Total collections', (select count(*) from collection_resource) as 'Collection resource relationships', (select count(*) from user) as 'Total users';
17Mail Log SELECT ml.ref, date, mail_to 'TO', IFNULL(u.username, 'SYSTEM') 'FROM', subject, sender_email FROM mail_log ml LEFT JOIN user u ON u.ref=ml.mail_from WHERE date>=date('[from-y]-[from-m]-[from-d]') and date<=adddate(date('[to-y]-[to-m]-[to-d]'),1) ORDER BY ml.ref DESC
18Resource comments SELECT c.ref 'Ref', c.created 'Date', c.resource_ref 'Resource ID', c.resource_ref 'thumbnail', c.body 'Comment', ifnull(concat(u.username,' (',u.fullname,')'),concat('ANONYMOUS: ',c.fullname,' (',c.website_url,')')) 'User', ifnull(c.email,u.email) Email FROM comment c LEFT JOIN user u ON u.ref=c.user_ref WHERE c.created>=date('[from-y]-[from-m]-[from-d]') and c.created<=adddate(date('[to-y]-[to-m]-[to-d]'),1) order by c.created DESC;
19File integrity check report SELECT * FROM (SELECT 'Oldest' AS State, ref 'Resource', ifnull(last_verified,'NEVER') 'Verified' FROM resource WHERE ref>0 AND integrity_fail=0 ORDER BY last_verified ASC LIMIT 1) AS a UNION ALL SELECT * FROM (SELECT 'Newest' AS State, ref 'Resource', ifnull(last_verified,'NEVER') 'Verified' FROM resource WHERE ref>0 AND integrity_fail=0 ORDER BY last_verified DESC LIMIT 1) AS b;

Please see the schema overview for context. This document was last updated on the 23rd of November 2020 at 19:35 (Europe/London time).