Moodle Query to Retrieve Outcomes Completed in Recent Weeks

To track outcome progress in Moodle for the past week, this ad-hoc aka custom query reports, lists the numbers of outcomes completed each week. It is formatted to show the weekly total for each course as shown in this image:

-- GROUP_CONCAT lists all the weeks and the standards completed in that week in a single table cell
-- By adding a order by in the group concat, we get the proper ordering of weeks (weeks were in the incorrect order without it)
-- CHAR(63) because the '?' is not accepted by Moodle Ad-Hoc Queries.

SELECT
    outcomes.courseid,
    CONCAT('', C.fullname,''
) as course_name,
    CONCAT(
        GROUP_CONCAT(
            CONCAT(outcomes.weekstr, concat(' [', outcomes.count, ']'))
            ORDER BY courseid ASC, w ASC
            SEPARATOR '
'
        ), '
Total:', SUM(outcomes.count)
    ) as outcomes_per_week
FROM
(

-- This sub query combines the three queries used by Moodle to get the outcome count
-- The WHERE statement
--  a) gets the day 16 weeks ago
--  b) We want to include results beginning on Sunday of that week
-- The SELECT weekstr - each day belongs to a week. We match each day with the start of its week.
-- eg. For the week of Wed Jan 5, the weekstr would be Sun-2

    SELECT
        goc.courseid, COUNT(gg.finalgrade) AS count,

        DATE_FORMAT(
            str_to_date(
                concat(year(from_unixtime(gg.timemodified)), LPAD(week(from_unixtime(gg.timemodified)), 2, 0), 'sunday'  ),
                '%X%V %W'),
            '%b-%e') weekstr,

        week(from_unixtime(gg.timemodified)) as w
    FROM prefix_grade_outcomes go
    JOIN prefix_grade_outcomes_courses goc
        ON go.id = goc.outcomeid
    JOIN prefix_grade_items as gi
        ON goc.outcomeid = gi.outcomeid AND
            gi.courseid = goc.courseid
    JOIN prefix_grade_grades as gg
        ON gg.itemid = gi.id
    WHERE gg.timemodified >= UNIX_TIMESTAMP(STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 week), '%Y%V Sunday'), '%X%V %W'))
    GROUP BY courseid, week(from_unixtime(gg.timemodified))
) as outcomes

JOIN prefix_course as C
    ON C.id = outcomes.courseid
GROUP BY courseid
ORDER BY C.fullname asc 

Note: Optimization can be done to move the weekstr to the outside query.

Run only a single instance of a cron job and prevent overlap

When dealing with web applications often a cron process must be executed at most once. If a cron job overlaps, database queries can cause performance issues or at worse deadlocks and stale processes.

Here is a script that solves this problem. Explained with detail as there are a lot of things happening in 5 lines of code.

#!/bin/bash

# Installation
# mkdir /var/run/moodle
# chown root.apache /var/run/moodle
# chmod 775 /var/run/moodle
# copy this file and make it executable by the cron user. 

# chmod this file 744

# add it to crontab or crontab -e -u apache 

# Explanation
# 1. set -e tells a bash script to exit whenever a non zero value is i
#  returned (0 means function executed without error)
# 2. flock needs 200 or any int to label the file descriptor
# 3. The ( brackets ) execs each line of commands in order and check to 
#  see if they return 0. 
# 4. 200> tells the fd 200 to create the lock file if it doesn't already 
#  exist 
# 5. -n nonblock, will return 1 if the lock is taken. as cron is being 
#  ran every 5-10 mins we can wait for the next one
# 6. trap, if cntrl-c is called or a command is killed, it will execute 

#  the command and exit. Our case it removes the lock file.

# Note: This works great for cron and flock files. May be an issue with 

#  race conditions if something other than flock eg. echo 'busy' > file.pid.

LOCKFILE=/var/run/moodle/moodlecron.lock
set -e
(
    flock -n 200 
    trap "rm $LOCKFILE" EXIT 
    # Add commands to execute
    /usr/bin/php /var/www/html/moodle/admin/cli/cron.php  
) 200>$LOCKFILE

Get your page to highlight in Moodle's navigation Menu

Moodle 2.3+ is able to automatically determine the menu items to highlight in the navigation menu. While working on one of my current projects, I was unable to make the current page highlight and I went looking for the Moodle method that do this.

I have a custom reports module that is located in report/mycustomreports

To add a custom report specific to a course and under the report menu item you add this bit of code to lib.php file.

// File report/mycustomreport/lib.php

function report_mycustomreports_extend_navigation_course($navigation, $course, $context) {
    global $CFG, $OUTPUT;
    if (has_capability('report/mycustomreports:viewcoursereports', $context)) {
        $url = new moodle_url('/report/mycustomreports/course/index.php', array('id'=>$course->id));
        $navigation->add(get_string('mycustomreports', 'report_mycustomreports'), $url, navigation_node::TYPE_SETTING, null, null, n
ew pix_icon('i/report', ''));
    }
}

Take note of the URL and how it will end up looking:
example.com/report/mycustomreports/course/index.php?id=55

Now to make sure that the link to this report in the courses menu receives a highlight and expands the window, you must add a $PAGE->set_url() in the index.php

// File: report/mycustomreports/course/index.php

$PAGE->set_url('/report/mycustomreports/course/index.php', array('id' => $course->id));

Both URL's now match.

If we click on the [Course Name] -> Reports -> My Custom Reports the page will load AND most importantly the link in the navigation menu will be highlighted.

Passwordless login with multiple id_rsa and ssh identities

Primarily for my benefit as this topic can be found on tons of google searches. I have different user names such as ~bunwich and ~sandwich on a variety of servers. I want to be able to login without a password by entering ssh bunwich@example1.com or ssh sandwich@example2.com

On my computer, I have two different id_rsa, one for each account. (Also the corresponding id_rsa.pub) The goal is to automatically choose the correct id_rsa for each username and login without a password.

1)  ssh allows you to manage multiple identies using a wild cards and filtering by remote hostname and remote username. You can also filter by local hostname and local username.

From the manual
"The file name may use the tilde syntax to refer to a user's home directory or one of the following escape characters: '%d' (local user's home directory), '%u' (local user name), '%l' (local host name), '%h' (remote host name) or '%r' (remote user name)."

2)  Create the following dirs

~/.ssh/ids/bunwich

~/.ssh/ids/sandwich

3) Copy the id_rsa belonging to each user into these dirs

~/.ssh/ids/bunwich/id_rsa

~/.ssh/ids/sandwich/id_rsa

chmod id_rsa to 600 if it isn't already

4)  Add an IdentityFile entry to you  ssh config file

vim ~/.ssh/config

Add the following line:

IdentityFile ~/.ssh/ids/%r/id_rsa
 

An alternative is to also include a host name for each username.

eg.

IdentityFile ~/.ssh/ids/%h/%r/id_rsa 

5) Now make sure that both servers example1 and example2 have a ~/.ssh/authorized_keys and you'll be able to do passwordless logins.

(You create authorized_keys by renaming the id_rsa.pub or appending the id_rsa.pub to the current authorized_keys)



Extra - While you're messing with your config file, why not add an extra visual measure to make sure your host hasn't changed.

VisualHostKey yes to ~/.ssh/config