Update Local DB with Emails from CMT Export of Reviewers

4th May 2014 Neil D. Lawrence

Reviewers can update their email address in CMT at any time. This is inconvenient because it's being used as the primary key to match reviewers to the local data base. This script tries to handle updates made in CMT and synchronize them across the local data base. It would be a lot easier to do this if we had access to the unique reviewer key stored in CMT (then we could use that as a primary key) but we don't seem to have access to that. In fact it was only very late on that I thought about it, and I haven't asked CMT if we can 'see' it.

In [ ]:
import cmtutils

To run this script, you need to export from CMT the list of users with IsReviewer=Yes (from the Manage Users section). This gives the entire list of reviewers in the system. Put the resulting file in the cmtutils.cmt_data_directory and give the name of the exported file to python below.

In [ ]:
filename = '2014-10-26_reviewer_export.xls'

Now we can read in the local data base and the export from CMT.

In [ ]:
# Read in the CMT export
cmt=cmtutils.cmt_reviewers_read(filename=filename)
# Open the reviewer data base
db = cmtutils.reviewerdb('reviewers.db')

Now we reset all the IsReviewer fields in the database to NULL in case any reviewer has been deleted in CMT.

In [ ]:
db.update_fields(db.ids(), 'IsReviewer', None)

Now we try and match reviewers from the CMT export to emails in the reviewer data base. If they don't match by email, try and match by firstname/lastname. We query the user as to whether to accept firstname/lastname matches automatically. If you answer no then that reviewer isn't updated, but you'll need to do some manual investigation to find out what's going on (it's probable that a CMT user has updated both their email and their firstname/lastname).

In [ ]:
for i, reviewer in cmt.reviewers.iterrows():
    id = db.match_or_add_reviewer(reviewer, match_firstname=True, primary_email=True, yes=True)
    if id:
        db.set_reviewer(id)
In [ ]:
for i, reviewer in cmt.reviewers.iterrows():
    id = db.match_reviewer(reviewer, match_firstname=True, primary_email=True)
    if id:
        db.set_reviewer(id)

Now perform a sanity check to count the number of users with IsReviewer=1 in the local data base. This should tally with the number of reviewers in CMT.

In [ ]:
selection_criteria = """IsReviewer=1"""
db._execute_sql('SELECT COUNT(Active) FROM Reviewers WHERE ' + selection_criteria + ';')
#db.export_reviewers(sql=selection_criteria)

If any reviewers have updated their emails, we need to re-export the list of CMT reviewers for the Toronto paper matching system. See this notebook to extract emails of reviewers missing from TPMS.

In [ ]:
db.export_tpm(sql=selection_criteria)

Updating Area Chair Emails

You can also do the same for area chairs. But normally they are all listed as reviewers anyway, so doing the above process works also for area chairs. You may need to run it, though, if you have deleted or added area chairs inside CMT. Again, the first thing you need is a the CMT export of area chairs (IsMetaReviewer=Yes from the Manage Users section). Specify that file below.

In [ ]:
filename_meta = '2014-10-26_meta_reviewer_export.xls'

Now update the local data base, just as above, with a sanity check performed at the end.

In [ ]:
cmt_meta= cmtutils.cmt_reviewers_read(filename=filename_meta)
# remove all meta reviewers.
db.update_fields(db.ids(), 'IsMetaReviewer', None)

# Update from the CMT export of meta reviewers.
for i, reviewer in cmt_meta.reviewers.iterrows():
    id = db.match_reviewer(reviewer, match_firstname=True, primary_email=True)
    if id:
        db.update_field(id, 'IsMetaReviewer', 1)

selection_criteria = """IsMetaReviewer=1"""
db._execute_sql('SELECT COUNT(Active) FROM Reviewers WHERE ' + selection_criteria + ';')
In [ ]:
selection_criteria = """IsMetaReviewer=1"""
db._execute_sql('SELECT COUNT(Active) FROM Reviewers WHERE ' + selection_criteria + ';')