List of reviewers with assigned submissions

Are you responsible for making OCS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, michael, John

Forum rules
The Public Knowledge Project Support Forum is moving to http://forum.pkp.sfu.ca

This forum will be maintained permanently as an archived historical resource, but all new questions should be added to the new forum. Questions will no longer be monitored on this old forum after March 30, 2015.
ramon
Posts: 945
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil
Contact:

List of reviewers with assigned submissions

Postby ramon » Mon Aug 29, 2011 11:25 am

Hello all,

Is it possible to add to the reviewer's list page (for the General Manager, under People > Roles > Reviewers) an option to list all the reviewers with active assignments (maybe add also with incomplete/incomplete, declined/not declined) so that I can send an email to those "groups" of reviewers?
How difficult would be to create a SQL statement so that I can email them manually?
I'm having trouble sending through OCS because of wrong registered email addresses or full inbox at the recipient's end.
So, I'd like to test emailing manually, but I have limitations on how many simultaneous messages I can send over my email server or GMAIL, for example.

Is "LEFT JOIN review_assignments as c ON a.user_id = c.reviewer_id" a correct assumption (that reviewer_id = user_id in their respective tables) in the following SQL?

Code: Select all

SELECT a.user_id, c.reviewer_id, a.username, a.email, CONCAT_ws(' ', a.first_name, a.middle_name, a.last_name) as fullname
FROM users as a
LEFT JOIN roles as b ON a.user_id = b.user_id
LEFT JOIN review_assignments as c ON a.user_id = c.reviewer_id
WHERE b.role_id = 4096
AND b.conference_id = 4
AND b.sched_conf_id = 4
AND a.user_id NOT IN (1, 2, 5)
AND YEAR(c.date_assigned) > 2010
AND c.date_completed IS NULL
GROUP BY a.username
ORDER BY a.user_id
LIMIT 0, 250

mcrider
Posts: 952
Joined: Mon May 05, 2008 10:29 am
Location: Vancouver, BC

Re: List of reviewers with assigned submissions

Postby mcrider » Wed Sep 07, 2011 1:23 pm

Hi Ramon,

Yes, that is the correct way to join review_assignments with users. Your SQL seems to work for me (after removing some of the ANDs that are specific to your journal). Are you having problems with this?

Cheers,
Matt

ramon
Posts: 945
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil
Contact:

Re: List of reviewers with assigned submissions

Postby ramon » Thu Sep 08, 2011 5:03 am

Hello Matt,

It worked here, but I just wanted to make sure it was correct.


Return to “OCS Technical Support”

Who is online

Users browsing this forum: No registered users and 1 guest