1,"Keywords used in resource edits","select 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;
"
2,"Keywords used in searches","select 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
"
3,"Resource 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
"
4,"Resource 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;
"
5,"Resources 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;
"
6,"Resources 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;
"
7,"Resources 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
"
8,"Resources 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
"
9,"Resources 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
"
10,"Resource 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
"
11,"Resource 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
"
12,"User details including group allocation","select 
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;
"
13,"Expired 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;
"
14,"Resources 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;
"
16,"Database 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';
"
17,"Mail 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
"
18,"Resource 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;"
19,"File 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;"
