If you have access to the database you could try running this query
SELECT distinctrow c.username,a.message, d.username
FROM moodlenew.mdl_message_read a
inner join moodlenew.mdl_message_read b on a.useridto = b.useridfrom
inner join moodlenew.mdl_user c on a.useridfrom=c.id
inner join moodlenew.mdl_user d on a.useridto=d.id
where a.timeread > unix_timestamp(date_add(curdate(),interval - 1 day))
Re: Messaging: What facilities are there for seeing users' messages?
by Ian Cannonier -
Number of replies: 16
In reply to Ian Cannonier
Re: Messaging: What facilities are there for seeing users' messages?
by Will H -
I've hacked Ian's database query around a bit, and made the results a bit prettier. The result is a quick 'n' dirty script that you can put in the /message directory, e.g. /message/listall.php
Perhaps the easiest way to get to it is to create a link to it by "adding a resource" to a course of your choice (I have one for admin links) and choosing "link to a file or web site".
By default it displays every message that has ever been sent, showing when it was sent, the sender and recipient, and when/if it was read. If you hover the mouse over a user's name, it will show you when he/she last accessed Moodle, and if you click on the name it will bring up their profile page.
You can also restrict it to the last 'n' days by calling it with the parameter, days=n, e.g. /message/listall.php?days=14 to show the last two weeks' worth.
Finally, it respects the existing permissions, only producing output if the currently logged in user has the "moodle/site:readallmessages" capability. You can also hide your link to it from students by making it a hidden resource (click on the little eye thing when you're editing the page). That way, it will appear grey (but useable) to teachers and admins, but invisible to students.
Edit: You can separate the read/unread messages by reversing the ORDER BY statement so it reads ORDER BY 5 ASC, 1 DESC;
Here's the code. Paste it all into a file using Notepad or TextWranger, or whatever, and upload it to your Moodle. My suggested location is /message but anywhere should work, as long as it can still find config.php.
Perhaps the easiest way to get to it is to create a link to it by "adding a resource" to a course of your choice (I have one for admin links) and choosing "link to a file or web site".
By default it displays every message that has ever been sent, showing when it was sent, the sender and recipient, and when/if it was read. If you hover the mouse over a user's name, it will show you when he/she last accessed Moodle, and if you click on the name it will bring up their profile page.
You can also restrict it to the last 'n' days by calling it with the parameter, days=n, e.g. /message/listall.php?days=14 to show the last two weeks' worth.
Finally, it respects the existing permissions, only producing output if the currently logged in user has the "moodle/site:readallmessages" capability. You can also hide your link to it from students by making it a hidden resource (click on the little eye thing when you're editing the page). That way, it will appear grey (but useable) to teachers and admins, but invisible to students.
Edit: You can separate the read/unread messages by reversing the ORDER BY statement so it reads ORDER BY 5 ASC, 1 DESC;
Here's the code. Paste it all into a file using Notepad or TextWranger, or whatever, and upload it to your Moodle. My suggested location is /message but anywhere should work, as long as it can still find config.php.
<?PHP
require_once('../config.php');
$context = get_context_instance(CONTEXT_SYSTEM);
require_capability('moodle/site:readallmessages', $context);
$PAGE = page_create_object(PAGE_COURSE_VIEW, 1); // Complete hack - no idea how to use this function properly
$PAGE->print_header('View all messages'); // Should probably be PAGE_ADMIN but I can't get this to work
if ( isset($_GET['days']) ) {
$qualifierr = 'WHERE msgr.timecreated > UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL - 10 DAY))';
$qualifieru = 'WHERE msgu.timecreated > UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL - 10 DAY))';
} else {
$qualifierr = '';
$qualifieru = '';
}
// SQL based on a suggestion by Ian Cannonier, in this thread: http://moodle.org/mod/forum/discuss.php?d=27559
$query = <<< END_OF_SQL
SELECT FROM_UNIXTIME(timecreated), senderr.username, message, recipientr.username, FROM_UNIXTIME(timeread), senderr.id, recipientr.id, FROM_UNIXTIME(senderr.lastaccess), FROM_UNIXTIME(recipientr.lastaccess)
FROM {$CFG->prefix}message_read msgr
INNER JOIN {$CFG->prefix}user senderr ON msgr.useridfrom=senderr.id
INNER JOIN {$CFG->prefix}user recipientr ON msgr.useridto=recipientr.id
$qualifierr
UNION ALL
SELECT FROM_UNIXTIME(timecreated), senderu.username, message, recipientu.username, 'unread', senderu.id, recipientu.id, FROM_UNIXTIME(senderu.lastaccess), FROM_UNIXTIME(recipientu.lastaccess)
FROM {$CFG->prefix}message msgu
INNER JOIN {$CFG->prefix}user senderu ON msgu.useridfrom=senderu.id
INNER JOIN {$CFG->prefix}user recipientu ON msgu.useridto=recipientu.id
$qualifieru
ORDER BY 1 DESC, 5 ASC;
END_OF_SQL;
if ( ! $connection = mysql_connect($CFG->dbhost, $CFG->dbuser, $CFG->dbpass) ) {
print_error("Could not connect to MySQL database");
}
if ( ! mysql_select_db($CFG->dbname) ) {
print_error("Could not select database");
}
// print_header(''); // This seems to be unnecessary / pointless
if ( ! $result = mysql_query($query) ) {
echo "<p>No messages have been sent</p>\n\n";
} else {
echo "<table border=\"1\">\n";
echo " <tr><th>Sent</th><th>From</th><th>To</th><th>Read</th><th>Message</th></tr>\n";
while( $row = mysql_fetch_array($result) ) {
echo " <tr>\n";
echo " <td align=\"center\">". substr($row[0],0,10) .'<br>'. substr($row[0],11,8) ."</td>\n";
echo " <td align=\"center\" nowrap><a href=\"{$CFG->wwwroot}/user/view.php?id={$row[5]}\" title=\"last access: {$row[7]}\">{$row[1]}</a></td>\n";
echo " <td align=\"center\" nowrap><a href=\"{$CFG->wwwroot}/user/view.php?id={$row[6]}\" title=\"last access: {$row[8]}\">{$row[3]}</a></td>\n";
echo " <td align=\"center\">". substr($row[4],0,10) .'<br>'. substr($row[4],11,8) ."</td>\n";
echo " <td>". str_replace("\n","<br>\n", $row[2]) ."</td>\n";
echo " </tr>\n";
}
echo "</table>\n";
}
print_footer();
?>
require_once('../config.php');
$context = get_context_instance(CONTEXT_SYSTEM);
require_capability('moodle/site:readallmessages', $context);
$PAGE = page_create_object(PAGE_COURSE_VIEW, 1); // Complete hack - no idea how to use this function properly
$PAGE->print_header('View all messages'); // Should probably be PAGE_ADMIN but I can't get this to work
if ( isset($_GET['days']) ) {
$qualifierr = 'WHERE msgr.timecreated > UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL - 10 DAY))';
$qualifieru = 'WHERE msgu.timecreated > UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL - 10 DAY))';
} else {
$qualifierr = '';
$qualifieru = '';
}
// SQL based on a suggestion by Ian Cannonier, in this thread: http://moodle.org/mod/forum/discuss.php?d=27559
$query = <<< END_OF_SQL
SELECT FROM_UNIXTIME(timecreated), senderr.username, message, recipientr.username, FROM_UNIXTIME(timeread), senderr.id, recipientr.id, FROM_UNIXTIME(senderr.lastaccess), FROM_UNIXTIME(recipientr.lastaccess)
FROM {$CFG->prefix}message_read msgr
INNER JOIN {$CFG->prefix}user senderr ON msgr.useridfrom=senderr.id
INNER JOIN {$CFG->prefix}user recipientr ON msgr.useridto=recipientr.id
$qualifierr
UNION ALL
SELECT FROM_UNIXTIME(timecreated), senderu.username, message, recipientu.username, 'unread', senderu.id, recipientu.id, FROM_UNIXTIME(senderu.lastaccess), FROM_UNIXTIME(recipientu.lastaccess)
FROM {$CFG->prefix}message msgu
INNER JOIN {$CFG->prefix}user senderu ON msgu.useridfrom=senderu.id
INNER JOIN {$CFG->prefix}user recipientu ON msgu.useridto=recipientu.id
$qualifieru
ORDER BY 1 DESC, 5 ASC;
END_OF_SQL;
if ( ! $connection = mysql_connect($CFG->dbhost, $CFG->dbuser, $CFG->dbpass) ) {
print_error("Could not connect to MySQL database");
}
if ( ! mysql_select_db($CFG->dbname) ) {
print_error("Could not select database");
}
// print_header(''); // This seems to be unnecessary / pointless
if ( ! $result = mysql_query($query) ) {
echo "<p>No messages have been sent</p>\n\n";
} else {
echo "<table border=\"1\">\n";
echo " <tr><th>Sent</th><th>From</th><th>To</th><th>Read</th><th>Message</th></tr>\n";
while( $row = mysql_fetch_array($result) ) {
echo " <tr>\n";
echo " <td align=\"center\">". substr($row[0],0,10) .'<br>'. substr($row[0],11,8) ."</td>\n";
echo " <td align=\"center\" nowrap><a href=\"{$CFG->wwwroot}/user/view.php?id={$row[5]}\" title=\"last access: {$row[7]}\">{$row[1]}</a></td>\n";
echo " <td align=\"center\" nowrap><a href=\"{$CFG->wwwroot}/user/view.php?id={$row[6]}\" title=\"last access: {$row[8]}\">{$row[3]}</a></td>\n";
echo " <td align=\"center\">". substr($row[4],0,10) .'<br>'. substr($row[4],11,8) ."</td>\n";
echo " <td>". str_replace("\n","<br>\n", $row[2]) ."</td>\n";
echo " </tr>\n";
}
echo "</table>\n";
}
print_footer();
?>
In reply to Will H
Re: Messaging: What facilities are there for seeing users' messages?
by Mike Worth -
Thanks for this- I've found it really useful. I have however added some extra links which I thought I'd share. Find my version attached
Mike
Mike
In reply to Mike Worth
Re: Messaging: What facilities are there for seeing users' messages?
by Will H -
Hi Mike,
I'm glad you found it useful.
Actually, your additions inspired me to give the whole thing a bit of an overhaul. In a nutshell, I've:
* incorporated your filters by sender, recipient, both or either
* fixed a bug to allow proper filtering by message age
* added the ability to sort results in various ways
* tidied the SQL generation code so there isn't so much duplication
* added a front end to the various options
I'm quite pleased with it but I haven't tested it extensively. Let me know what you think.
-Will.
EDIT: Filtering by user is now done by username so, for example, I might filter with 'joebloggs' instead of having to remember that Joe's user ID is 274.
I'm glad you found it useful.
Actually, your additions inspired me to give the whole thing a bit of an overhaul. In a nutshell, I've:
* incorporated your filters by sender, recipient, both or either
* fixed a bug to allow proper filtering by message age
* added the ability to sort results in various ways
* tidied the SQL generation code so there isn't so much duplication
* added a front end to the various options
I'm quite pleased with it but I haven't tested it extensively. Let me know what you think.
-Will.
EDIT: Filtering by user is now done by username so, for example, I might filter with 'joebloggs' instead of having to remember that Joe's user ID is 274.
A couple of further tweaks:
* I've added a pair of links to each row to quickly allow you to re-filter to see all messages involving sender or recipient
* I've added a link at the top the quickly remove all filters
Latest version attached.
* I've added a pair of links to each row to quickly allow you to re-filter to see all messages involving sender or recipient
* I've added a link at the top the quickly remove all filters
Latest version attached.
In reply to Will H
Re: Messaging: What facilities are there for seeing users' messages?
by Mike Worth -
Thanks for that- it looks a lot neater than mine 
Mike
Mike
In reply to Will H
Re: Messaging: What facilities are there for seeing users' messages?
by Eric Kemsley -
Very nice work!!! Thanks!!
In reply to Will H
Re: Messaging: What facilities are there for seeing users' messages?
by Mike Worth -
I've had a bit of a fiddle with the code so that it displays actual names rather than usernames. I've also changed the direct mysql access to use moodle's get_records_sql function; as a consiquence it may now work on other databases.
Mike
Mike
In reply to Mike Worth
Re: Messaging: What facilities are there for seeing users' messages?
by Matt Campbell -
Mike & Will H,
I've been looking for a good messaging log report and this one does the trick quite well.
What do you think about setting this up as a report to drop into admin/report, and adding an export option of some sort?
It would be nice to see this in contrib.
Thanks,
Matt
I've been looking for a good messaging log report and this one does the trick quite well.
What do you think about setting this up as a report to drop into admin/report, and adding an export option of some sort?
It would be nice to see this in contrib.
Thanks,
Matt
In reply to Matt Campbell
Re: Messaging: What facilities are there for seeing users' messages?
by Mike Worth -
You make a good point about the tracker- I've added it: CONTRIB-1143
As far as admin reports go, I haven't really played with them- might well be worth looking into at some point
Thanks,
Mike
As far as admin reports go, I haven't really played with them- might well be worth looking into at some point
Thanks,
Mike
In reply to Mike Worth
Re: Messaging: What facilities are there for seeing users' messages?
by Matt Campbell -
I've got it working as an admin report, will add it to the tracker.
Thanks,
Matt
Thanks,
Matt
In reply to Mike Worth
Re: Messaging: What facilities are there for seeing users' messages?
by Toby White -
Hi, I'd just like to say thank you for sorting this script out. Got it implemented and set up in no time.... all working perfectly!
In reply to Will H
Re: Messaging: What facilities are there for seeing users' messages?
by Jean-Pierre Pawlak -
I've been using listall.php since a couple of months.
Always worked like a charm.
Since a recent server upgrade (php 5.2.9, MySQL 5.0.67 and apache 2.2.11) I'm getting an internal server error.
Any idea how to solve this?
Always worked like a charm.
Since a recent server upgrade (php 5.2.9, MySQL 5.0.67 and apache 2.2.11) I'm getting an internal server error.
Any idea how to solve this?
In reply to Ian Cannonier
Re: Messaging: What facilities are there for seeing users' messages?
by Mike Worth -
I've been using a very similar query myself (I didn't find this until after I'd done it myself) but it isn't showing all the messages, whereas if I click on the 'message write' link that is in the logs I can see the message history complete with missing messages.
Anyone got any idea what's going on?
Thanks,
Mike
Anyone got any idea what's going on?
Thanks,
Mike
In reply to Mike Worth
Re: Messaging: What facilities are there for seeing users' messages?
by Will H -
Unread messages and read messages are stored in separate tables. That's why mine does the UNION to combine the results from both.
In reply to Will H
Re: Messaging: What facilities are there for seeing users' messages?
by Mike Worth -
Ah- I'd missed that. I'm using your script now and it works grand.
Thanks,
Mike
Thanks,
Mike