OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



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
What to do if you have a technical problem with OCS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved. Please note that this FAQ is OJS-centric, but most issues are applicable to both platforms.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OCS Conference Support and Discussion subforum; if you have a development question, try the OCS Development subforum.

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
ramon
 
Posts: 940
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil

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
mcrider
 
Posts: 952
Joined: Mon May 05, 2008 10:29 am
Location: Vancouver, BC

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.
ramon
 
Posts: 940
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil


Return to OCS Technical Support

Who is online

Users browsing this forum: No registered users and 2 guests