mikebuchanon

select mc.shortname as course_Id,mu.username, 
case when ABS(ROUND(mgg.finalgrade, 0) - mgg.finalgrade) <= 0.0000005 then cast(mgg.finalgrade as text) else 'Pending...' end as final_grade,
mgi.itemname
from mdl_grade_grades mgg
join mdl_user mu on mgg.userid=mu.id
join mdl_user_enrolments mue on mue.userid=mu.id
join mdl_grade_items mgi on mgg.itemid=mgi.id
join mdl_course mc on mc.id=mgi.courseid
join mdl_enrol me on mue.enrolid=me.id and me.courseid=mc.id
join mdl_role_assignments mra on mu.id=mra.userid

mikebuchanon

##To configure log rotation:##

1. Log in as root or use sudo to execute the following commands.
2. Create a file called zendserver using a text editor and save it in /etc/logrotate.d/, with the following content:

/usr/local/zend/var/log/*.log { 
  size 5M 
  missingok 
  rotate 10 
 compress 

mikebuchanon

    public function count() {
        global $DB;
        if ($this->totalcommentcount === null) {
            $this->totalcommentcount = $DB->count_records('comments', array('itemid' => $this->itemid, 'commentarea' => $this->commentarea, 'contextid' => $this->context->id));
        }
        return $this->totalcommentcount;
    }

mikebuchanon

with user_stats as (
select extract(dow from to_timestamp(time)) as day_of_week,
extract(hour from to_timestamp(time)) as hour_of_day,
count(1) as logins
from mdl_log
where action='login'
and to_timestamp(time) > '6/30/2012'
group by extract(dow from to_timestamp(time)), extract(hour from to_timestamp(time))
)
select day_of_week, hour_of_day, avg(logins) as avg_logins

mikebuchanon

--update grades for all assignments for all courses in a given category
update mdl_assignment --update assignment object (front end)
set grade = 100
where course in 
(
'34','32','66','65','23','67' --list of courses
);

update mdl_grade_items --update gradebook (back end)
set grademax=100

mikebuchanon

select to_timestamp(ml.time)::date as date,
extract(dow from to_timestamp(ml.time)) as dow,
extract(hour from to_timestamp(ml.time)) as hour,
sum(case when mr.name='Student' then 1 else 0 end) as student_logins,
sum(case when mr.name='Teacher' then 1 else 0 end) as fac_staff_logins
from mdl_log ml
join mdl_context mcon on mcon.contextlevel=50 and mcon.instanceid=ml.course
join mdl_role_assignments mra on mra.contextid=mcon.id
join mdl_role mr on mr.id=mra.roleid
where to_timestamp(ml.time) > '1/14/2013'

mikebuchanon

select mc.shortname, mcs.name 
from mdl_course_sections mcs
join mdl_course mc on mc.id=mcs.course
where mcs.name like E'%\xe2\x80\x93%' --en dash
or mcs.name like E'%\xe2\x80\x94%' --em dash
or mcs.name like E'%\xe2\x80\x9c%' --left double quote
or mcs.name like E'%\xe2\x80\x9d%' --right double quote

mikebuchanon

SELECT d.id AS discussion,
f.name AS name,
sum(case when r.postid is null then 1 else 0 end) as unread_posts,
sum(case when r.postid is null then 0 else 1 end) as read_posts
FROM mdl_course c
JOIN mdl_forum_discussions d on d.course = c.id
JOIN mdl_forum f on f.id = d.forum
JOIN mdl_forum_posts p ON p.discussion = d.id
LEFT JOIN mdl_forum_read r ON r.postid = p.id
WHERE c.id = 3

mikebuchanon

select usename, nspname || '.' || relname as relation,
       case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type,
       priv
from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
     pg_user,
     (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
where relkind in ('r', 'v')
      and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
      and not (nspname ~ '^pg_' or nspname = 'information_schema')
      and relname ilike 'mdl_assign%' and usename ='reporting'

mikebuchanon

select mc.shortname, mqq.content "question", 
sum(case when mqqc.content = 'Strongly Disagree' then 1 else 0 end) "Strongly Disagree",
sum(case when mqqc.content = 'Disagree' then 1 else 0 end) "Disagree",
sum(case when mqqc.content = 'Neutral' then 1 else 0 end) "Neutral",
sum(case when mqqc.content = 'Agree' then 1 else 0 end) "Agree",
sum(case when mqqc.content = 'Strongly Agree' then 1 else 0 end) "Strongly Agree",
count(mqqc.content) "Total"
from mdl_course mc
join mdl_questionnaire mq on mq.course=mc.id
join mdl_questionnaire_question mqq on mqq.survey_id=mq.id and mqq.deleted = 'n' and mqq.type_id=4