Library App.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from datetime import timedelta, date, datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Opened database successfully \n",
      "\n",
      "1) Find item\n",
      "2) Item checkout\n",
      "3) Item return\n",
      "4) Item donation\n",
      "5) Find event\n",
      "6) Event registration\n",
      "7) Volunteer\n",
      "8) Ask a librarian\n",
      "9) Due dates and active fines\n",
      "10) Pay fines\n",
      "11) Sign up\n",
      "12) Shut Down\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Helper function\n",
    "def checkPersonID(conn, ID):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT ID FROM Person\")\n",
    "        rows = cur.fetchall()\n",
    "        \n",
    "        for row in rows:\n",
    "            if(row[0] == ID): return True\n",
    "        return False\n",
    "\n",
    "\n",
    "# Helper function\n",
    "def userInputID(conn):\n",
    "    while True: \n",
    "        try:\n",
    "            personID = int(input(\"Enter your ID: \"))\n",
    "        except ValueError:\n",
    "            print(\"Not a number! Try again.\")\n",
    "            continue\n",
    "        if (checkPersonID(conn, personID)):\n",
    "            break\n",
    "        else: \n",
    "            print(\"Not a valid ID! Try again.\")\n",
    "    return personID\n",
    "\n",
    "    \n",
    "\n",
    "# Helper function\n",
    "def checkEventID(conn, ID):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT ID FROM Event WHERE ID = \" + str(ID))\n",
    "        if(cur.fetchall()): return True\n",
    "        return False\n",
    "    \n",
    "\n",
    "\n",
    "# Helper function\n",
    "def checkItemCopy(conn, itemID, copyID):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT * FROM ItemCopy WHERE itemID = \" +\\\n",
    "                   str(itemID) + \" AND copyNum = \" + str(copyID))\n",
    "        if(cur.fetchall()): return True\n",
    "        return False\n",
    "    \n",
    "    \n",
    "    \n",
    "# Helper function\n",
    "def isItemCopyAvailable(conn, itemID, copyID):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT itemID, copyNum, status FROM ItemCopy WHERE itemID = \" +\\\n",
    "                    str(itemID) + \" AND copyNum = \" + str(copyID))\n",
    "        \n",
    "        if(cur.fetchall()[0][2] == 'Available'): return True\n",
    "        return False    \n",
    "    \n",
    "\n",
    "        \n",
    "# Helper function\n",
    "def getItemID(conn, title, author):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT ID FROM Item WHERE title='\" + title + \\\n",
    "            \"' AND author = '\" + author + \"'\")\n",
    "        rows=cur.fetchall()\n",
    "\n",
    "        if rows: return rows[0][0]\n",
    "        else: return None\n",
    "\n",
    "        \n",
    "\n",
    "# Helper function        \n",
    "def isItemCheckedOut(conn, itemID, personID, copyID):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT * FROM CheckedOut WHERE itemID = \" +\\\n",
    "                    str(itemID) + \" AND personID = \" + str(personID) + \\\n",
    "                    \" AND copyID = \" + str(copyID))\n",
    "        if(cur.fetchall()): return True\n",
    "        return False  \n",
    "\n",
    "    \n",
    "    \n",
    "# Helper function\n",
    "def hasPersonCheckedOut(conn, personID):\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT * FROM CheckedOut WHERE personID = \" + str(personID))\n",
    "        if(cur.fetchall()): return True\n",
    "        return False  \n",
    "    \n",
    " \n",
    "    \n",
    "# Helper function\n",
    "def calculateFine(conn, itemID, personID, copyID):\n",
    "    dueDate = ''\n",
    "    fine = 0\n",
    "    today = datetime.strptime(str(date.today()), '%Y-%m-%d')\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT dueDate FROM Dues WHERE itemID = \" +\\\n",
    "                    str(itemID) + \" AND personID = \" + str(personID) + \\\n",
    "                    \" AND copyID = \" + str(copyID))\n",
    "        result = cur.fetchall()\n",
    "        if(result): dueDate = datetime.strptime(result[0][0], '%Y-%m-%d')\n",
    "        else: return\n",
    "        \n",
    "        fine = (today-dueDate).days\n",
    "        if(fine <= 0): return 0\n",
    "        else: return fine\n",
    "        \n",
    "        \n",
    "    \n",
    "def itemSearch(conn):    \n",
    "    print(\"\\n>>>Find item\")\n",
    "    itemSearch = str(input(\"Enter Title: \"))\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        myQuery = \"SELECT * FROM Item WHERE title=:itemTitle\"\n",
    "        cur.execute(myQuery,{\"itemTitle\":itemSearch})\n",
    "        rows=cur.fetchall()\n",
    "        \n",
    "        if rows:\n",
    "            print(\"\\nWe have the following item/s with the title '\" + itemSearch + \"': \")\n",
    "        else:\n",
    "            print(\"\\nUnfortunately, we do not have any items with the title '\" + itemSearch + \"'\\n\")\n",
    "\n",
    "        for row in rows:\n",
    "            print('Item ID: ' + str(row[0]))\n",
    "            print('Title: ' + row[1])\n",
    "            print('Author: ' + row[2])\n",
    "            print('Edition: ' + str(row[3]))\n",
    "            print('Year: ' + str(row[4]))\n",
    "            print('Genre: ' + str(row[5]))\n",
    "            print('Total Copies: ' + str(row[6]))\n",
    "            print('Available Copies: ' + str(row[7]))\n",
    "            print(\"\\n\")\n",
    "\n",
    "    \n",
    "        \n",
    "def itemCheckout(conn):\n",
    "    print(\"\\n>>>Checkout Item\")\n",
    "    personID = userInputID(conn)\n",
    "    itemID = None\n",
    "    copyID = None\n",
    "    \n",
    "    # Check if itemID and copyID is valid and available\n",
    "    while True: \n",
    "        try:\n",
    "            itemID = int(input(\"Enter item ID located in the back of the book: \"))\n",
    "            copyID =  int(input(\"Enter copy ID located in the back of the book: \"))\n",
    "        except ValueError:\n",
    "            print(\"Not a number! Try again.\")\n",
    "            continue\n",
    "        if (checkItemCopy(conn, itemID, copyID)):\n",
    "            if (isItemCopyAvailable(conn, itemID, copyID)):\n",
    "                break\n",
    "            else:\n",
    "                print(\"That copy is unavailable, try again.\\n\")\n",
    "        else: \n",
    "            print(\"Not a valid ID! Try again.\")\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        \n",
    "        # User has 3 weeks until they have to return the item without fines\n",
    "        dueDate = date.today() + timedelta(days=21)\n",
    "        \n",
    "        # Insert to CheckedOut and Dues in Library.db\n",
    "        cur.execute(\"INSERT INTO Dues (itemID, personID, copyID, dueDate) VALUES (\" + \\\n",
    "            str(itemID) + \", \" + str(personID) + \", \" + str(copyID) + \", '\" + str(dueDate) + \"')\")\n",
    "        cur.execute(\"INSERT INTO CheckedOut (itemID, personID, copyID) VALUES (\" + \\\n",
    "            str(itemID) + \", \" + str(personID) + \", \" + str(copyID) + \")\")\n",
    "        cur.execute(\"SELECT numAvailable FROM Item WHERE ID = \" + str(itemID) )\n",
    "        numAvailable = cur.fetchall()[0][0] - 1\n",
    "        cur.execute(\"UPDATE Item SET numAvailable = \" + str(numAvailable) + \" WHERE ID = \" + str(itemID) )\n",
    "        cur.execute(\"UPDATE ItemCopy SET status = 'Not Available' WHERE itemID = \" + \\\n",
    "                    str(itemID) + \" AND copyNum = \" + str(copyID))\n",
    "        print(\"\\nCheckout successful! Your item is due on \" + str(dueDate)+ \"\\n\")\n",
    "    \n",
    "    \n",
    "       \n",
    "def itemReturn(conn):\n",
    "    # add fines to owes\n",
    "    print(\"\\n>>>Return Item\")\n",
    "    personID = userInputID(conn)\n",
    "    itemID = None\n",
    "    copyID = None\n",
    "    \n",
    "    # Check if the person has a copy to return\n",
    "    if(hasPersonCheckedOut(conn, personID) == False):\n",
    "        print(\"You have not checked anything out. You have nothing to return.\\n\")\n",
    "        return\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        \n",
    "        # Show user list of items and due dates \n",
    "        cur.execute(\"SELECT itemID, copyID, title, dueDate FROM Dues, Item WHERE\" + \\\n",
    "                    \" itemID = ID AND personID = \" + str(personID))\n",
    "        rows = cur.fetchall()\n",
    "        print(\"\\nYour checked out items and their due dates:\")\n",
    "        if rows:\n",
    "            for row in rows:\n",
    "                print(\"Item ID: \" + str(row[0]) + \" ,copy ID: \"+ str(row[1]) + \\\n",
    "                      \" ,title: \" + row[2] + \" is due on \" + str(row[3]))\n",
    "                \n",
    "        # Check if item copy has been checked out\n",
    "        while True: \n",
    "            try:\n",
    "                itemID = int(input(\"Enter item ID located in the back of the book: \"))\n",
    "                copyID =  int(input(\"Enter copy ID located in the back of the book: \"))\n",
    "            except ValueError:\n",
    "                print(\"Not a number! Try again.\")\n",
    "                continue\n",
    "            if (isItemCheckedOut(conn, itemID, personID, copyID)):\n",
    "                break\n",
    "            else: \n",
    "                print(\"You have not checked out that copy. Try again.\")\n",
    "        \n",
    "        # Delete from CheckedOut and Dues, Update copy's status, Update numAvailable for item\n",
    "        # Calculate person's fines\n",
    "        fine = calculateFine(conn, itemID, personID, copyID)\n",
    "        cur.execute(\"DELETE FROM CheckedOut WHERE itemID = \" + str(itemID) +\\\n",
    "                    \" AND personID = \" + str(personID) + \" AND copyID = \" + str(copyID))\n",
    "        cur.execute(\"DELETE FROM Dues WHERE itemID = \" + str(itemID) +\\\n",
    "                    \" AND personID = \" + str(personID) + \" AND copyID = \" + str(copyID))\n",
    "        cur.execute(\"UPDATE ItemCopy SET status = 'Available' WHERE itemID = \" + \\\n",
    "                    str(itemID) + \" AND copyNum = \" + str(copyID))\n",
    "        cur.execute(\"SELECT numAvailable FROM Item WHERE ID = \" + str(itemID) )\n",
    "        numAvailable = cur.fetchall()[0][0] + 1\n",
    "        cur.execute(\"UPDATE Item SET numAvailable = \" + str(numAvailable) + \" WHERE ID = \" + str(itemID))\n",
    "        cur.execute(\"SELECT owes FROM Person WHERE ID = \" + str(personID))\n",
    "        owes = cur.fetchall()[0][0] + fine\n",
    "        cur.execute(\"UPDATE Person SET owes = \" + str(owes) + \" WHERE ID = \" + str(personID))\n",
    "        \n",
    "        print(\"Thanks for returning an item!\\n\")\n",
    "\n",
    "    \n",
    "    \n",
    "def itemDonation(conn):\n",
    "    print(\"\\n>>>Donate Item\")\n",
    "    print(\"Fill in all fields below (NULL if it is unknown)\")\n",
    "    \n",
    "    # Begin user input and make sure \"   \" is not accepted\n",
    "    while True:\n",
    "        title = input(\"Title: \")\n",
    "        if(title.isspace() or title == \"\"):\n",
    "            print(\"Fill in the title field.\")\n",
    "        else: break\n",
    "    while True:\n",
    "        author = input(\"Author: \")\n",
    "        if(author.isspace() or author == \"\"):\n",
    "            print(\"Fill in the author field.\")\n",
    "        else: break\n",
    "    while True:\n",
    "        edition = input(\"Edition: \")\n",
    "        if(edition.isspace() or edition == \"\"):\n",
    "            print(\"Fill in the edition field.\")\n",
    "        else: break\n",
    "    while True: # User only has two options for genre\n",
    "        genre = input(\"Genre (FIC or NON FIC): \")\n",
    "        if (genre == 'FIC' or genre == 'NON FIC'): break\n",
    "        else:\n",
    "            print(\"Input not valid! Try again.\")\n",
    "            continue\n",
    "    while True: \n",
    "        try:\n",
    "            year = int(input(\"Publication year: \"))\n",
    "        except ValueError:\n",
    "            print(\"Not a number! Try again.\")\n",
    "            continue\n",
    "        else:\n",
    "            break\n",
    "    # End of user input\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        ID = getItemID(conn, title, author)\n",
    "\n",
    "        # Add a copy if item is already in the system\n",
    "        if (ID != None):\n",
    "            cur.execute(\"SELECT quantity, numAvailable FROM Item WHERE ID =\" + str(ID))\n",
    "            result = cur.fetchall()\n",
    "            copyNum = result[0][0] + 1\n",
    "            numAvailable = result[0][1] + 1\n",
    "\n",
    "            cur.execute(\"INSERT INTO ItemCopy (itemID, copyNum, type, status) VALUES (\" + \\\n",
    "                str(ID) + \", \" + str(copyNum) + \", 'print', 'Available')\")\n",
    "            cur.execute(\"UPDATE Item SET quantity =\" + str(copyNum) + \", numAvailable = \" + \\\n",
    "                        str(numAvailable) + \" WHERE ID = \" + str(ID))\n",
    "        \n",
    "        # Add new item and add a copy if donation is not already in the library\n",
    "        else:\n",
    "            cur.execute(\"SELECT MAX(ID) FROM Item\")\n",
    "            ID = cur.fetchall()[0][0] + 1\n",
    "            cur.execute(\"INSERT INTO Item (ID, title, author, edition, year, genre, \" + \\\n",
    "                        \"quantity, numAvailable) VALUES (\" + str(ID) + \", '\" + title + \\\n",
    "                        \"', '\" + author + \"', '\" + edition + \"', \" + str(year) + \\\n",
    "                        \", '\" + genre + \"', 1, 1)\")\n",
    "            cur.execute(\"INSERT INTO ItemCopy (itemID, copyNum, type, status) VALUES (\" + \\\n",
    "                str(ID) + \", 1, 'print', 'Available')\")\n",
    "        print(\"\\nThank you for donating!\\n\")\n",
    "    \n",
    "    \n",
    "    \n",
    "def showEvents(conn):  \n",
    "    print(\"\\n>>>All Events:\")\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT * FROM Event\")\n",
    "        rows=cur.fetchall()\n",
    "        \n",
    "        # Show User all events\n",
    "        for row in rows:\n",
    "            print('Event ID: '+ str(row[0]))\n",
    "            print('Event: ' + row[1])\n",
    "            print('Date: ' + str(row[2]))\n",
    "            print('Room: ' + str(row[3]))\n",
    "            print('Time: ' + str(row[4]) + \"-\"+ row[5])\n",
    "            print('Fee: ' + str(row[6]))\n",
    "            print('Recommended Age: ' + str(row[7]))\n",
    "            print(\"\\n\")\n",
    "\n",
    "\n",
    "\n",
    "def eventRegistration(conn):\n",
    "    print(\"\\n>>>Event Registration:\")\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "            \n",
    "        try:\n",
    "            personID = userInputID(conn)\n",
    "            while True: \n",
    "                try:\n",
    "                    eventID = int(input(\"Enter event ID: \"))\n",
    "                except ValueError:\n",
    "                    print(\"Not a number! Try again.\")\n",
    "                    continue\n",
    "                if (checkEventID(conn, eventID)):\n",
    "                    break\n",
    "                else: \n",
    "                    print(\"Not a valid ID! Try again.\")        \n",
    "           \n",
    "            # Attempt to insert\n",
    "            cur.execute(\"INSERT INTO Registered (personID, eventID) VALUES (\" + \\\n",
    "                str(personID) + \", \" + str(eventID) + \")\")\n",
    "        \n",
    "        except sqlite3.IntegrityError:\n",
    "            print(\"You are already registered into that event.\\n\")\n",
    "            return\n",
    "        else:    \n",
    "            print(\"Registration Successful.\\n\")\n",
    "    \n",
    "    \n",
    "    \n",
    "def findEvent(conn):\n",
    "    print(\"\\n>>>Find Event\")\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        event= input(\"Enter event name: \")\n",
    "        cur.execute(\"SELECT * FROM Event WHERE eventName = '\" + event + \"'\")\n",
    "        rows=cur.fetchall()\n",
    "        \n",
    "        if rows:\n",
    "            print(\"\\nWe have the following events with the name '\" + event + \"': \")\n",
    "       \n",
    "            for row in rows:\n",
    "                print('Event ID: '+ str(row[0]))\n",
    "                print('Event: ' + row[1])\n",
    "                print('Date: ' + str(row[2]))\n",
    "                print('Room: ' + str(row[3]))\n",
    "                print('Time: ' + str(row[4]) + \"-\"+ row[5])\n",
    "                print('Fee: ' + str(row[6]))\n",
    "                print('Recommended Age: ' + str(row[7]))\n",
    "                print(\"\\n\")\n",
    "            eventRegistration(conn)\n",
    "            \n",
    "        else:\n",
    "            print(\"Unfortunately, we do not have any events with the name '\" + event + \"'\\n\")       \n",
    "    \n",
    "    \n",
    "    \n",
    "def volunteer(conn):\n",
    "    print(\"\\n>>>Volunteer\")\n",
    "    \n",
    "    # Begin user input\n",
    "    print(\"Please enter your:\")\n",
    "    firstName = input(\"First name: \")\n",
    "    lastName = input(\"Last name: \")\n",
    "    while True: # Make sure age is a number\n",
    "        try:\n",
    "            age = int(input(\"Age: \"))\n",
    "        except ValueError:\n",
    "            print(\"Not a number! Try again.\")\n",
    "            continue\n",
    "        else:\n",
    "            break\n",
    "    while True: # User only has two options for gender\n",
    "        gender = input(\"Gender (M or F): \")\n",
    "        if (gender == 'M' or gender == 'F'): break\n",
    "        else:\n",
    "            print(\"Input not valid! Try again.\")\n",
    "            continue\n",
    "    # End of user input\n",
    "            \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        \n",
    "        # Getting an id number for the volunteer\n",
    "        cur.execute(\"SELECT MAX(ID) FROM Staff\")\n",
    "        ID = cur.fetchall()[0][0] + 1\n",
    "\n",
    "        # Inserting user input to staff with the role as 'Volunteer'\n",
    "        cur.execute(\"INSERT INTO Staff (ID, firstName, lastName, age, gender, role)\" + \\\n",
    "            \"VALUES (\" + str(ID) + \", '\" + firstName + \"', '\" + lastName + \"', '\" + \\\n",
    "            str(age) + \"', '\" + gender + \"', 'Volunteer')\")\n",
    "        \n",
    "        print(\"\\nThank you for signing up to be a volunteer.\"+ \\\n",
    "              \" We will email you further details within 7 days\\n\")\n",
    "    \n",
    "    \n",
    "    \n",
    "def askLibrarian(conn):     \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT firstName FROM Staff WHERE role='Librarian'\")\n",
    "        rows=cur.fetchall()\n",
    "        librarians = ''\n",
    "        \n",
    "        for row in rows:\n",
    "            librarians += row[0] + \", \"\n",
    "        \n",
    "        if rows:\n",
    "            print(\"\\nOne of \" + librarians + \"will be right with you.\\n\")\n",
    "        else:\n",
    "            print(\"\\nUnfortunately, there are no available librarians at the moment.\\n\")\n",
    "\n",
    "\n",
    "            \n",
    "def duesAndFines(conn):\n",
    "    personID = userInputID(conn)\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        fine = 0\n",
    "        \n",
    "        # Show user list of items and due dates \n",
    "        cur.execute(\"SELECT title, author, dueDate FROM Dues, Item WHERE\" + \\\n",
    "                    \" itemID = ID AND personID = \" + str(personID))\n",
    "        rows = cur.fetchall()\n",
    "        print(\"\\nYour checked out items and their due dates:\")\n",
    "        if rows:\n",
    "            for row in rows:\n",
    "                print(row[0]+\" by \" + row[1] + \" is due on \" + row[2])\n",
    "                \n",
    "        # Show user amount owed\n",
    "        cur.execute(\"SELECT itemID, personID, copyID FROM Dues WHERE personID = \" + str(personID))\n",
    "        rows = cur.fetchall()\n",
    "        if rows:\n",
    "            for row in rows:\n",
    "                fine += calculateFine(conn, row[0], row[1], row[2])\n",
    "        cur.execute(\"SELECT owes FROM Person WHERE ID = \" + str(personID))\n",
    "        owes = cur.fetchall()[0][0] + fine\n",
    "        print(\"You owe the library $\" + str(owes) + \".00\\n\")\n",
    "            \n",
    "   \n",
    "    \n",
    "def payFines(conn):\n",
    "    personID = userInputID(conn)\n",
    "    \n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT owes FROM Person WHERE ID = \" + str(personID))\n",
    "        owes = cur.fetchall()[0][0]\n",
    "        print(\"\\nYou owe the library $\" + str(owes) + \".00\")\n",
    "        print(\"Active fines with currently checked out books are not included.\\n\")\n",
    "        \n",
    "        if(owes == 0): return\n",
    "        else:\n",
    "            while True: # User can only pay amount <= owes\n",
    "                pay = int(input(\"Pay amount: \"))\n",
    "                if (pay <= owes): break\n",
    "                else:\n",
    "                    print(\"You are overpaying. Try again.\")\n",
    "                    continue\n",
    "            cur.execute(\"UPDATE Person SET owes = \" + str(owes - pay) + \\\n",
    "                        \" WHERE ID = \" + str(personID))\n",
    "            cur.execute(\"SELECT owes FROM Person WHERE ID = \" + str(personID))\n",
    "            owes = cur.fetchall()[0][0]\n",
    "            print(\"You owe the library $\" + str(owes) + \".00 after your payment\\n\")\n",
    "        \n",
    "        \n",
    "\n",
    "def signUp(conn):\n",
    "    print(\"\\n>>>Sign up\")\n",
    "    print(\"Fill in all fields below.\")\n",
    "    firstName = input(\"First name: \")\n",
    "    lastName = input(\"Last name: \")\n",
    "    while True: # Make sure age is a number\n",
    "        try:\n",
    "            age = int(input(\"Age: \"))\n",
    "        except ValueError:\n",
    "            print(\"Not a number! Try again.\")\n",
    "            continue\n",
    "        else:\n",
    "            break\n",
    "    \n",
    "    # Insert new user to database of people\n",
    "    with conn:\n",
    "        cur = conn.cursor()\n",
    "        \n",
    "        # Getting an id number for the new user\n",
    "        cur.execute(\"SELECT MAX(ID) FROM Person\")\n",
    "        ID = cur.fetchall()[0][0] + 1\n",
    "        cur.execute(\"INSERT INTO Person (ID, firstName, lastName, age)\" + \\\n",
    "            \"VALUES (\" + str(ID) + \", '\" + firstName + \"', '\" + lastName + \\\n",
    "            \"', \" + str(age) + \")\")\n",
    "        print(\"Thank you for signing up. Enjoy the library!\\n\")\n",
    "        \n",
    "        \n",
    "        \n",
    "def userAction(conn):\n",
    "    # User choices\n",
    "    print(\"1) Find item\\n2) Item checkout\\n3) Item return\\n4) Item donation\\n5)\",\n",
    "          \"Find event\\n6) Event registration\\n7) Volunteer\\n\" + \\\n",
    "          \"8) Ask a librarian\\n9) Due dates and active fines\\n10)\"+ \\\n",
    "          \" Pay fines\\n11) Sign up\\n12) Shut Down\\n\")\n",
    "    \n",
    "    # Make sure user input is an integer\n",
    "    while True:\n",
    "        try:\n",
    "            option = int(input(\"Choose an option by entering a number: \"))\n",
    "        except ValueError:\n",
    "            print(\"Not a number! Try again.\")\n",
    "            continue\n",
    "        else:\n",
    "            if(option < 1 or option > 12):\n",
    "                print(\"Not a valid option! Try again.\")\n",
    "                continue\n",
    "            break   \n",
    "    \n",
    "    if(option == 1): itemSearch(conn)\n",
    "    if(option == 2): itemCheckout(conn)\n",
    "    if(option == 3): itemReturn(conn)\n",
    "    if(option == 4): itemDonation(conn)\n",
    "    if(option == 5): findEvent(conn)\n",
    "    if(option == 6): \n",
    "        showEvents(conn)\n",
    "        eventRegistration(conn)\n",
    "    if(option == 7): volunteer(conn)    \n",
    "    if(option == 8): askLibrarian(conn)\n",
    "    if(option == 9): duesAndFines(conn)    \n",
    "    if(option == 10): payFines(conn)    \n",
    "    if(option == 11): signUp(conn)\n",
    "    if(option == 12): return\n",
    "\n",
    "    userAction(conn)\n",
    "    \n",
    "    \n",
    "    \n",
    "def main():  \n",
    "    conn = sqlite3.connect('library.db')\n",
    "    cursor = conn.cursor()\n",
    "    print(\"\\nOpened database successfully \\n\")\n",
    "    \n",
    "    userAction(conn)\n",
    "\n",
    "    if conn:\n",
    "        conn.close()\n",
    "        print(\"Closed database successfully\\n\")\n",
    "    print(\"Thank you for visiting the library. Happy reading!\")\n",
    "        \n",
    "if __name__ == '__main__':\n",
    "    main()"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}