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