timeRegistrationManager.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from ipywidgets import Layout, Button, Box, Label, Text, Output, Tab\n",
    "from IPython.display import display\n",
    "from timeStamp import rfidTimeStamp as rfidModule\n",
    "import sqlite3 \n",
    "import time\n",
    "import datetime\n",
    "\n",
    "class timeRegistration(rfidModule):\n",
    "    '''\n",
    "        timeRegistration is a class that contains the UI. Functionality:\n",
    "        \n",
    "        1) Create new users in the data base\n",
    "        2) Modify time registration of existing users. This function is made to allow write into the database should the user\n",
    "        accidentially forget to log in or log out\n",
    "        3) Users time registration data can be extracted within time period and allow report generation followed by sending report over email. \n",
    "    '''\n",
    "    def __init__(self, dbPath):\n",
    "        \n",
    "        #Connect to the database\n",
    "        self.dbPath = dbPath\n",
    "        self.createDatabaseIfNotExist(dbPath)\n",
    "        \n",
    "        #Timeout meaning how long time to wait for an action before going into idle mode\n",
    "        self.timeout = 10 #in sec\n",
    "        self.timeFormat = \"%Y%m%d%H%M%S\"\n",
    "        \n",
    "        #Initializing the GUI\n",
    "        self.buildGUI()\n",
    "    \n",
    "    def buildGUI(self):\n",
    "        \n",
    "        #REGISTER NEW EMPLOYEE\n",
    "        form_item_layout = Layout(\n",
    "        display = 'flex',\n",
    "        flex_flow = 'row',\n",
    "        justify_content = 'space-between')\n",
    "        \n",
    "        self.employeeRegNameText = Text(placeholder = 'Employee full name', disabled = False)\n",
    "        self.employeeRegButton = Button(description = 'Register', disabled = False, \n",
    "                                        button_style = 'success',\n",
    "                                        tooltip = 'Register new employee card')\n",
    "        \n",
    "        self.employeeRegButton.on_click(self.registerNewEmployee)\n",
    "        \n",
    "        self.employeeRegOut = Output()\n",
    "        \n",
    "        self.employeeRegFormItems = [\n",
    "            Box([Label(value = 'New employee name* : '), self.employeeRegNameText]),\n",
    "            Box([self.employeeRegButton]),\n",
    "            Box([self.employeeRegOut])\n",
    "        ]\n",
    "        \n",
    "        self.registerForm = Box(self.employeeRegFormItems, layout = Layout(\n",
    "        display = 'flex',\n",
    "        flex_flow = 'column',\n",
    "        border = 'solid 2px',\n",
    "        align_items = 'stretch',\n",
    "        width = '70%'))\n",
    "        \n",
    "        #TAB\n",
    "        self.tab = Tab()\n",
    "        self.tab.children = [self.registerForm]\n",
    "        self.tab.set_title(0, 'Register')\n",
    "        \n",
    "        display(self.tab)\n",
    "        \n",
    "    def createDatabaseIfNotExist(self, databasePath):\n",
    "        '''\n",
    "        Check if the table of the data base provided by the databasePath exists. If not it will be created.\n",
    "        '''\n",
    "        conn = sqlite3.connect(databasePath)\n",
    "        sqlString = '''CREATE TABLE IF NOT EXISTS workinghour (name text, employeeNo text, datetime text, status text)'''\n",
    "        c = conn.cursor()\n",
    "        c.execute(sqlString)\n",
    "        conn.commit()\n",
    "        \n",
    "        sqlString = '''CREATE TABLE IF NOT EXISTS employee (name text, employeeNo)'''\n",
    "        c.execute(sqlString)\n",
    "        conn.commit()\n",
    "        conn.close()\n",
    "    \n",
    "    def connectDataBase(self):\n",
    "        '''\n",
    "        connects to the sql data base\n",
    "        '''\n",
    "        conn = sqlite3.connect(self.dbPath)\n",
    "        c = conn.cursor()\n",
    "        return conn, c\n",
    "        \n",
    "    def writeEmployeeToDb(self, cardString):\n",
    "        '''\n",
    "            Register the employee into sql data base. The cardString is a string from the card in the format\n",
    "            employee name;employee number; check in status\n",
    "        '''\n",
    "        cardDict = self.splitCardString(cardString)\n",
    "        sqlString = f''' INSERT INTO employee(name, employeeNo)\n",
    "              VALUES(\"{cardDict['name']}\", \"{cardDict['employeeNumber']}\")'''\n",
    "        \n",
    "        conn,c = self.connectDataBase()\n",
    "        c.execute(sqlString)\n",
    "        conn.commit()\n",
    "        conn.close()\n",
    "            \n",
    "    def registerNewEmployee(self, change):\n",
    "        #Extract employee full name from the text\n",
    "        employeeName = self.employeeRegNameText.value\n",
    "        if not employeeName:\n",
    "            with self.employeeRegOut:\n",
    "                self.employeeRegOut.clear_output()\n",
    "                print('Error! Employee name is blank!')\n",
    "                return\n",
    "            \n",
    "        #Generate a unique employee number. The number is based on date of employment.\n",
    "        now = datetime.datetime.now()\n",
    "        employeeNumber = now.strftime(self.timeFormat)\n",
    "        checkInStatus = 'registered' #First time when the employee is registered, the check in status is set to registered\n",
    "        #put employee name, employee number and checkin status into one string and write it to the card\n",
    "        stringToCard = employeeName.strip() + ';' + employeeNumber.strip() + ';' + checkInStatus\n",
    "        \n",
    "        with self.employeeRegOut:\n",
    "            self.employeeRegOut.clear_output()\n",
    "            print('Ready to register ' + employeeName + ' swipe the card to the reader')\n",
    "            idnum, textin = self.writeCard(stringToCard, timeout = self.timeout)\n",
    "            \n",
    "            if idnum is not None:\n",
    "                #Write the employee into the data base\n",
    "                self.writeEmployeeToDb(stringToCard)\n",
    "                print(employeeName + ' has been registered.')\n",
    "                print('Employee number is ' + employeeNumber)\n",
    "                print('Complete!')\n",
    "            else:\n",
    "                print('Timeout ' + employeeName + ' not registered. Try again...')\n",
    "            \n",
    "\n",
    "obj = timeRegistration('employeeHour.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Oliver Wu', '20210107190143'), ('Elin Wu', '20210107190258')]\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('employeeHour.db')\n",
    "c = conn.cursor()\n",
    "c = conn.execute('select * from employee')\n",
    "data = c.fetchall()\n",
    "print(data)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "GPIO.cleanup()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}