Update DB from CMT Export.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:b91798b091ee83bb9d871eca04c4565aa6072178797fbc0b474698ccbf54e0cb"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Update Local DB with Emails from CMT Export of Reviewers\n",
      "\n",
      "### 4th May 2014 Neil D. Lawrence\n",
      "\n",
      "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."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import cmtutils"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "To run this script, you need to export from CMT the list of users with `IsReviewer=Yes` (from the [Manage Users](https://cmt.research.microsoft.com/NIPS2014/Protected/Chair/Users/ManageUsers.aspx) 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. "
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "filename = '2014-10-26_reviewer_export.xls'"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now we can read in the local data base and the export from CMT."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Read in the CMT export\n",
      "cmt=cmtutils.cmt_reviewers_read(filename=filename)\n",
      "# Open the reviewer data base\n",
      "db = cmtutils.reviewerdb('reviewers.db')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now we reset all the `IsReviewer` fields in the database to NULL in case any reviewer has been deleted in CMT."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db.update_fields(db.ids(), 'IsReviewer', None)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "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)."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for i, reviewer in cmt.reviewers.iterrows():\n",
      "    id = db.match_or_add_reviewer(reviewer, match_firstname=True, primary_email=True, yes=True)\n",
      "    if id:\n",
      "        db.set_reviewer(id)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for i, reviewer in cmt.reviewers.iterrows():\n",
      "    id = db.match_reviewer(reviewer, match_firstname=True, primary_email=True)\n",
      "    if id:\n",
      "        db.set_reviewer(id)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "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."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selection_criteria = \"\"\"IsReviewer=1\"\"\"\n",
      "db._execute_sql('SELECT COUNT(Active) FROM Reviewers WHERE ' + selection_criteria + ';')\n",
      "#db.export_reviewers(sql=selection_criteria)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "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](./Remind Missing TPMS.ipynb) to extract emails of reviewers missing from TPMS."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db.export_tpm(sql=selection_criteria)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Updating Area Chair Emails\n",
      "\n",
      "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](https://cmt.research.microsoft.com/NIPS2014/Protected/Chair/Users/ManageUsers.aspx) section). Specify that file below. "
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "filename_meta = '2014-10-26_meta_reviewer_export.xls'"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now update the local data base, just as above, with a sanity check performed at the end."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "\n",
      "cmt_meta= cmtutils.cmt_reviewers_read(filename=filename_meta)\n",
      "# remove all meta reviewers.\n",
      "db.update_fields(db.ids(), 'IsMetaReviewer', None)\n",
      "\n",
      "# Update from the CMT export of meta reviewers.\n",
      "for i, reviewer in cmt_meta.reviewers.iterrows():\n",
      "    id = db.match_reviewer(reviewer, match_firstname=True, primary_email=True)\n",
      "    if id:\n",
      "        db.update_field(id, 'IsMetaReviewer', 1)\n",
      "\n",
      "selection_criteria = \"\"\"IsMetaReviewer=1\"\"\"\n",
      "db._execute_sql('SELECT COUNT(Active) FROM Reviewers WHERE ' + selection_criteria + ';')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selection_criteria = \"\"\"IsMetaReviewer=1\"\"\"\n",
      "db._execute_sql('SELECT COUNT(Active) FROM Reviewers WHERE ' + selection_criteria + ';')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}