tool_walkthrough.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import six\n",
    "from matplotlib.backends.backend_pdf import PdfPages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add UID to complete list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#midterm_list = pd.read_csv('wus_midterm_users_and_passwords.csv')\n",
    "klausur_list = pd.read_excel('Anmeldungen.xlsx')\n",
    "all_list = pd.read_excel('al-grundlagenvonwahrscheinlichkeitstheorieundstatistik.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(columns=['Name','FB02UID','Matrikel','Raum','Platz','Date'])\n",
    "for i in range(len(klausur_list)):        \n",
    "    exam_matrikel = klausur_list.Matrikel[i]\n",
    "    exam_nachname = klausur_list.Nachname[i]\n",
    "    exam_vorname = klausur_list.Vorname[i]\n",
    "    exam_name = exam_vorname + ' ' + exam_nachname\n",
    "    exam_raum = klausur_list.Raum[i]\n",
    "    exam_platz = klausur_list.Platz[i]\n",
    "    date = \"22.03.2019\"\n",
    "    #print (exam_matrikel, exam_nachname, exam_nachname, exam_raum, exam_platz)\n",
    "    \n",
    "    for j in range(len(all_list)):\n",
    "        fb02_matrikel = all_list.Matrikel[j]\n",
    "        fb02_uid = all_list.UID[j]\n",
    "        fb02_uid = fb02_uid.replace(\" \", \"\")\n",
    "        if fb02_matrikel == exam_matrikel:\n",
    "            df_new = pd.DataFrame([[exam_name,fb02_uid,exam_matrikel,exam_raum,exam_platz,date]], \n",
    "                            columns=['Name','FB02UID','Matrikel','Raum','Platz','Date'])\n",
    "            df = df.append(df_new, ignore_index=True)\n",
    "\n",
    "df.to_csv('exam_complete_list.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Update Platz and Raum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_list = pd.read_csv('wus_final_username_and_password.csv')\n",
    "updated_list = pd.read_excel('Anmeldungen_final.xlsx')\n",
    "\n",
    "df = pd.DataFrame(columns=['Name','FB02UID','Username','Password','Matrikel','Raum','Platz','Date','Session'])\n",
    "for i in range(len(final_list)):\n",
    "    final_name = final_list.Name[i]\n",
    "    final_fb02uid = final_list.FB02UID[i]\n",
    "    final_username = final_list.Username[i]\n",
    "    final_password = final_list.Password[i]\n",
    "    final_matrikel = final_list.Matrikel[i]\n",
    "    final_raum = final_list.Raum[i]\n",
    "    final_platz = final_list.Platz[i]\n",
    "    final_date = final_list.Date[i]\n",
    "    \n",
    "    for j in range(len(updated_list)):\n",
    "        fb02_matrikel = updated_list.Matrikel[j]\n",
    "        fb02_uid = updated_list.FB02UID[j]\n",
    "        fb02_uid = fb02_uid.replace(\" \", \"\")\n",
    "        fb02_raum = updated_list.Raum[j]\n",
    "        fb02_platz = updated_list.Platz[j]\n",
    "        fb02_session = updated_list.Session[j]\n",
    "        if (int(final_matrikel) == int(fb02_matrikel)):\n",
    "            #print (final_fb02uid, fb02_uid)\n",
    "            #print (int(final_matrikel), int(fb02_matrikel))\n",
    "            df_new = pd.DataFrame([[final_name,final_fb02uid,final_username, final_password,\n",
    "                                    final_matrikel,fb02_raum,fb02_platz,final_date, fb02_session]], \n",
    "                            columns=['Name','FB02UID','Username','Password','Matrikel','Raum','Platz','Date','Session'])\n",
    "            df = df.append(df_new, ignore_index=True)\n",
    "            #print (\"Got\", final_fb02uid)\n",
    "            \n",
    "df.to_csv('wus_final_username_and_password_new.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create attendance list\n",
    "#### Create attendance list during the exam based"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "time = '15.30-18.00'\n",
    "root_dir = '/home/wus/resources/wus_midterm_ws19'\n",
    "final_list = pd.read_csv('{}/{}-list-output.csv'.format(root_dir, time))\n",
    "#updated_list = pd.read_excel('Anmeldungen_final.xlsx')\n",
    "\n",
    "df_1 = pd.DataFrame(columns=['Name','Raum/Platz','Zeit','Unterschrift','Abgegeben','Info'])\n",
    "df_2 = pd.DataFrame(columns=['Name','Raum/Platz','Zeit','Unterschrift','Abgegeben','Info'])\n",
    "df_3 = pd.DataFrame(columns=['Name','Raum/Platz','Zeit','Unterschrift','Abgegeben','Info'])\n",
    "raumen = ['C063', 'C175', 'C177']\n",
    "for i in range(len(final_list)):\n",
    "    final_name = final_list.Name[i]\n",
    "    final_raum = final_list.Raum[i].strip()\n",
    "    final_platz = final_list.Platz[i]\n",
    "    final_sess = time\n",
    "    #print (final_sess)\n",
    "    if final_raum == raumen[0]:\n",
    "        df_new_1 = pd.DataFrame([[final_name,final_raum+'/'+str(final_platz),final_sess,' ', ' ','']], \n",
    "                        columns=['Name','Raum/Platz','Zeit','Unterschrift','Abgegeben','Info'])\n",
    "        df_1 = df_1.append(df_new_1, ignore_index=True)\n",
    "    \n",
    "    if final_raum == raumen[1]:\n",
    "        df_new_2 = pd.DataFrame([[final_name,final_raum+'/'+str(final_platz),final_sess,' ', ' ','']], \n",
    "                        columns=['Name','Raum/Platz','Zeit','Unterschrift','Abgegeben','Info'])\n",
    "        df_2 = df_2.append(df_new_2, ignore_index=True)\n",
    "    \n",
    "    if final_raum == raumen[2]:\n",
    "        df_new_3 = pd.DataFrame([[final_name,final_raum+'/'+str(final_platz),final_sess,' ', ' ','']], \n",
    "                        columns=['Name','Raum/Platz','Zeit','Unterschrift','Abgegeben','Info'])\n",
    "        df_3 = df_3.append(df_new_3, ignore_index=True)\n",
    "\n",
    "#df_1 = df_1.sort_values(['Sess'])     \n",
    "df_1.to_csv('{}/attendance_{}_{}.csv'.format(root_dir, raumen[0],time), index=False)\n",
    "#df_2 = df_2.sort_values(['Sess'])\n",
    "df_2.to_csv('{}/attendance_{}_{}.csv'.format(root_dir, raumen[1],time), index=False)\n",
    "#df_3 = df_3.sort_values(['Sess'])\n",
    "df_3.to_csv('{}/attendance_{}_{}.csv'.format(root_dir, raumen[2],time), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hashcode list\n",
    "#### Usage: after the exam students need to write down their hashcode on this list with their signature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "time = '15.30-18.00'\n",
    "root_dir = '/home/wus/resources/wus_midterm_ws19'\n",
    "final_list = pd.read_csv('{}/{}-list-output.csv'.format(root_dir, time))\n",
    "\n",
    "df_1 = pd.DataFrame(columns=['Name','Raum/Platz','Zeit','Hashcode','Unterschrift'])\n",
    "df_2 = pd.DataFrame(columns=['Name','Raum/Platz','Zeit','Hashcode','Unterschrift'])\n",
    "df_3 = pd.DataFrame(columns=['Name','Raum/Platz','Zeit','Hashcode','Unterschrift'])\n",
    "raumen = ['C063', 'C175', 'C177']\n",
    "\n",
    "for i in range(len(final_list)):\n",
    "    final_name = final_list.Name[i]\n",
    "    final_raum = final_list.Raum[i].strip()\n",
    "    final_platz = final_list.Platz[i]\n",
    "    final_sess = time\n",
    "    #print (final_sess)\n",
    "    if final_raum == raumen[0]:\n",
    "        df_new_1 = pd.DataFrame([[final_name,final_raum+'/'+str(final_platz),final_sess,' ', ' ']], \n",
    "                        columns=['Name','Raum/Platz','Zeit','Hashcode','Unterschrift'])\n",
    "        df_1 = df_1.append(df_new_1, ignore_index=True)\n",
    "    \n",
    "    if final_raum == raumen[1]:\n",
    "        df_new_2 = pd.DataFrame([[final_name,final_raum+'/'+str(final_platz),final_sess,' ', ' ']], \n",
    "                        columns=['Name','Raum/Platz','Zeit','Hashcode','Unterschrift'])\n",
    "        df_2 = df_2.append(df_new_2, ignore_index=True)\n",
    "    \n",
    "    if final_raum == raumen[2]:\n",
    "        df_new_3 = pd.DataFrame([[final_name,final_raum+'/'+str(final_platz),final_sess,' ', ' ']], \n",
    "                        columns=['Name','Raum/Platz','Zeit','Hashcode','Unterschrift'])\n",
    "        df_3 = df_3.append(df_new_3, ignore_index=True)\n",
    "\n",
    "df_1.to_csv('{}/hashcode_{}_{}.csv'.format(root_dir, raumen[0],time), index=False)\n",
    "df_2.to_csv('{}/hashcode_{}_{}.csv'.format(root_dir, raumen[1],time), index=False)\n",
    "df_3.to_csv('{}/hashcode_{}_{}.csv'.format(root_dir, raumen[2],time), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exam sheet generation (CSV to PDF)\n",
    "\n",
    "#### Create a pdf of the student information (e.g. Username, password, hashcode, exam info, etc) for every user\n",
    "#### This code will generate a pdf containing the student information on a single page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import Sequence\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import six\n",
    "import imageio\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.backends.backend_pdf import PdfPages\n",
    "import argparse\n",
    "\n",
    "def generate_exam_sheet(data: pd.core.frame.DataFrame,\n",
    "                        info:pd.core.frame.DataFrame,\n",
    "                        course_name: str=None,\n",
    "                        semester: str =None,\n",
    "                        col_width: float=9.5,\n",
    "                        row_height: float=7.225,\n",
    "                        font_size: float=28,\n",
    "                        header_color: str='#dddddd',\n",
    "                        row_colors: Sequence[str]=['#f1f1f2', 'w'],\n",
    "                        edge_color: str='w',\n",
    "                        bbox: Sequence[float]=[0., 0., 0.9, 1.],\n",
    "                        header_columns: int=0,\n",
    "                        axes: Sequence=None,\n",
    "                        take_home_sheet: bool=False,\n",
    "                        **kwargs) -> matplotlib.figure.Figure:\n",
    "    '''Returns a matplotlib figure that displays an exam sheet.\n",
    "    The sheet includes:\n",
    "        * the university logo on top,\n",
    "        * the name of the course and the semester in which the exam is conducted\n",
    "        * the student name and login information\n",
    "        * information about the date and location of the exam for the student\n",
    "        * an empty space for the exam submission hash code\n",
    "        * an empty space for the exam submission time stamp\n",
    "    '''\n",
    "    if axes is None:\n",
    "        size = (np.array(data.shape[::-1]) + np.array([0, 1])) * np.array([col_width, row_height])\n",
    "        fig, axes = plt.subplots(8,1, figsize=size)\n",
    "        for i in range(8):\n",
    "            axes[i].axis('off')\n",
    "    \n",
    "    \n",
    "    # we add the university logo on top of the exam sheet\n",
    "    axes[0].imshow(imageio.imread('./exam_user_utilities/figures/hbrs_logo.png'))\n",
    "\n",
    "    tables = dict()\n",
    "    \n",
    "    tables['course'] = axes[2].table(cellText=[[semester]], \n",
    "                                     bbox=bbox, colLabels=[course_name],\n",
    "                                     cellLoc='center', **kwargs)\n",
    "    if take_home_sheet:\n",
    "        tables['sheet_info'] = axes[1].table(cellText=[['Zum Mitnehmen']], bbox=[0., 0., 0.125, 1.],\n",
    "                                         colLabels=[u'To take home'],\n",
    "                                         cellLoc='left', loc='left',**kwargs)\n",
    "        tables['user_data'] = axes[3].table(cellText=[['{}'.format(data.values[0][0])]], bbox=bbox,\n",
    "                                              colLabels=[u'{}'.format(data.columns[0])],\n",
    "                                              cellLoc='center', **kwargs)\n",
    "        tables['exam_info'] = axes[4].table(cellText=[['{}'.format(data.values[0][2])]], bbox=bbox,\n",
    "                                              colLabels=[u'{}'.format(data.columns[2])],\n",
    "                                              cellLoc='center', **kwargs)\n",
    "        tables['hashcode'] = axes[5].table(cellText=[['']], bbox=bbox,\n",
    "                                           colLabels=[u'Hashcode'],\n",
    "                                           cellLoc='center', **kwargs)\n",
    "        tables['timestamp'] = axes[6].table(cellText=[['']], bbox=bbox,\n",
    "                                            colLabels=[u'Timestamp (HH:MM:SS)'],\n",
    "                                            cellLoc='center', **kwargs)\n",
    "        \n",
    "    else:\n",
    "        tables['sheet_info'] = axes[1].table(cellText=[['Bitte geben Sie diesen Zettel bei der Aufsicht ab']], \n",
    "                                             bbox=[0., 0., 0.4, 1.],\n",
    "                                             colLabels=[u'This sheet has to be returned to the exam supervisor'],\n",
    "                                             loc='left',**kwargs)\n",
    "        tables['user_data'] = axes[3].table(cellText=data.values, bbox=bbox,\n",
    "                                            colLabels=data.columns,\n",
    "                                            cellLoc='center', **kwargs)\n",
    "        tables['exam_info'] = axes[4].table(cellText=info.values, bbox=bbox,\n",
    "                                            colLabels=info.columns,\n",
    "                                            cellLoc='center', **kwargs)\n",
    "        tables['hashcode'] = axes[5].table(cellText=[['', '']], bbox=bbox,\n",
    "                                            colLabels=[u'Hashcode', \n",
    "                                                       u'Timestamp (HH:MM:SS)'],\n",
    "                                            cellLoc='center', **kwargs)\n",
    "        tables['hardware_info'] = axes[6].table(cellText=[['', '']], bbox=bbox,\n",
    "                                            colLabels=[u'Laptop number (laptop pool)', \n",
    "                                                       u'USB stick number (laptop pool)'],\n",
    "                                            cellLoc='center', **kwargs)\n",
    "        tables['signature'] = axes[7].table(cellText=[['', '']], bbox=bbox,\n",
    "                                            colLabels=[u'Signature (Unterschrift)', u'Notes'],\n",
    "                                            cellLoc='center', **kwargs)\n",
    "\n",
    "    for table_name in tables:\n",
    "        tables[table_name].auto_set_font_size(False)\n",
    "        tables[table_name].set_fontsize(font_size)\n",
    "\n",
    "    for table_name in tables:\n",
    "        for k, cell in six.iteritems(tables[table_name]._cells):\n",
    "            cell.set_edgecolor(edge_color)\n",
    "            if k[0] == 0 or k[1] < header_columns:\n",
    "                cell.set_text_props(weight='bold', color='black')\n",
    "                cell.set_facecolor(header_color)\n",
    "    \n",
    "    return fig\n",
    "\n",
    "def generate_exam_sheets(course_name: str, semester: str,\n",
    "                         csv_student_list_file: str,\n",
    "                         exam_sheet_file: str) -> None:\n",
    "    '''Generates a PDF file (saved as exam_sheet_file)\n",
    "    with exam sheets for each student.\n",
    "\n",
    "    The PDF file is generated using data from csv_student_list_file,\n",
    "    whose header is given as\n",
    "\n",
    "        Name,FB02UID,Username,Password,Matrikel,Raum,Platz,Date\n",
    "\n",
    "    and has a separate entry for each student.\n",
    "\n",
    "    Keyword arguments:\n",
    "    course_name: str -- name of the course\n",
    "    semester: str -- semester of the exam\n",
    "    csv_student_list_file: str -- name of a CSV file containing\n",
    "                                  student data\n",
    "    exam_sheet_file: str -- name of the output PDF file\n",
    "                            with the exam sheets\n",
    "\n",
    "    '''\n",
    "    student_list = pd.read_csv(csv_student_list_file)\n",
    "    pp = PdfPages(exam_sheet_file)\n",
    "    for i in range(len(student_list)):\n",
    "        print('Generating exam sheet {0}/{1}'.format(i+1, len(student_list)))\n",
    "\n",
    "        name = student_list.Name[i]\n",
    "        fb02uid = student_list.FB02UID[i]\n",
    "        username = student_list.Username[i]\n",
    "        password = student_list.Password[i]\n",
    "\n",
    "        matrikelnummer = student_list.Matrikel[i]\n",
    "        raum = student_list.Raum[i]\n",
    "        platz = student_list.Platz[i]\n",
    "        date = student_list.Date[i]\n",
    "\n",
    "        session = None\n",
    "        if 'Session' in student_list.index:\n",
    "            session = student_list.Session[i]\n",
    "\n",
    "        data_df = pd.DataFrame([[name,fb02uid,username,password]],\n",
    "                               columns=['Name', 'FB02UID', 'Username', 'Password'])\n",
    "\n",
    "        room_str = '{0} ({1})'.format(raum, str(session)) if session else raum\n",
    "        info_df = pd.DataFrame([[matrikelnummer, room_str, platz,date]],\n",
    "                               columns=['Matrikelnummer', 'Raum', 'Platz', 'Datum'])\n",
    "\n",
    "        pp.savefig(generate_exam_sheet(data_df, info_df,\n",
    "                                       course_name=course_name,\n",
    "                                       semester=semester))\n",
    "        pp.savefig(generate_exam_sheet(data_df, info_df,\n",
    "                                       course_name=course_name,\n",
    "                                       semester=semester,\n",
    "                                       take_home_sheet=True))\n",
    "    pp.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_student_list_file = './exam_user_utilities/examples/student_exam_data.csv'\n",
    "exam_sheet_file = './exam_user_utilities/examples/student_exam_sheet.pdf'\n",
    "\n",
    "course_name = 'My awesome course'\n",
    "semester = 'Sommersemester 2019'\n",
    "generate_exam_sheets(course_name, semester,\n",
    "                     csv_student_list_file,\n",
    "                     exam_sheet_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Verify login for all users\n",
    "#### Automatic login verification for all users\n",
    "#### Usage: specify the server and the user list (in csv) where one of the columns has Name, FB02UID, Username, Password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import argparse\n",
    "import yaml\n",
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "def check_login(login_url, username, password, timeout=15.0):\n",
    "    try:\n",
    "        response = requests.post(login_url, \n",
    "                            data={'username': username, 'password': password}, \n",
    "                            timeout=timeout, verify=False)\n",
    "    except requests.ReadTimeout:\n",
    "        print(\"timeout after {} seconds when trying to log in user '{}' at URL '{}'\".format(timeout, username, login_url))\n",
    "        return False\n",
    "\n",
    "    if response.status_code != 200:\n",
    "        print(\"failed to send POST request for user '{}' to URL '{}'\".format(username, login_url))\n",
    "        return False\n",
    "\n",
    "    if response.text.find('Invalid username or password') > -1:\n",
    "        return False\n",
    "\n",
    "    return True\n",
    "\n",
    "def main(server, csv_student_list):\n",
    "    print('checking logins at server: ' + server)\n",
    "    student_list = pd.read_csv(csv_student_list)\n",
    "    login_url = os.path.join(server, 'hub', 'login?next=')\n",
    "    for i in range(len(student_list)):\n",
    "        name = student_list.Name[i]\n",
    "        fb02uid = student_list.FB02UID[i]\n",
    "        username = student_list.Username[i]\n",
    "        password = student_list.Password[i]\n",
    "        \n",
    "        if check_login(login_url, username, password):\n",
    "            print(\"login successful: '{}'\".format(username))\n",
    "        else:\n",
    "            print(\"login failed: '{}'\".format(username))\n",
    "    print (\"Login check is done\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "server ='https://10.20.168.222/' #local server\n",
    "csv_student_list = '/home/emha/hbrs/WiMitarbeiter/Courses/MRC/SS20/MRC-Exam.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "\n",
    "main(server, csv_student_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}