Menu GUI.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a927fc27",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "You have reached the maximum number of attempts allowed.\n",
      "Please enter your email address so that you can reset your password: logan.lauton@marquette.edu\n",
      "Check you email\n"
     ]
    }
   ],
   "source": [
    "##Logan Lauton\n",
    "##Menu UI\n",
    "##COSC 3810 - 101\n",
    "\n",
    "##importing required packages for the menu's GUI    \n",
    "from datetime import date\n",
    "import datetime\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "import getpass\n",
    "from IPython.display import clear_output\n",
    "import logging\n",
    "import os\n",
    "import oracledb\n",
    "import pandas as pd\n",
    "import requests\n",
    "import smtplib\n",
    "import sys\n",
    "import time\n",
    "\n",
    "##beginning with simple output showing a welcome statement for the menu \n",
    "print(\"Welcome to the Mental Health Database\")\n",
    "attempts = 0\n",
    "##Startup Menu\n",
    "while True:\n",
    "    \n",
    "    ##outputting one character at a time for look\n",
    "    string = (\"Input your Username:\")\n",
    "    for char in string:\n",
    "        print(char, end='')\n",
    "        time.sleep(.05)\n",
    "    \n",
    "    ##gathering username\n",
    "    user = getpass.getpass()\n",
    "\n",
    "    ##outputting one character at a time for look\n",
    "    string = (\"Input your Password:\",)\n",
    "    for char in string:\n",
    "        print(char, end='')\n",
    "        time.sleep(.05)\n",
    "    \n",
    "    ##gathering password\n",
    "    password = getpass.getpass()\n",
    "    ##number of login attempts allowed\n",
    "    if attempts < 3:\n",
    "        try:\n",
    "            os.environ.get('TNS_ADMIN')\n",
    "            connection = oracledb.connect(\n",
    "                user=user,\n",
    "                password=password,\n",
    "                dsn = \"ms73dyswf309gljx_high\",\n",
    "                config_dir=\"./config\",\n",
    "                wallet_location=\"./config\",\n",
    "                wallet_password=\"6YW?}$.5H(N*+vj6\")\n",
    "            print(\"Welcome \" + user.title())\n",
    "            break\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            time.sleep(3)\n",
    "            clear_output()\n",
    "            print(\"Try Re-entering\")\n",
    "            attempts = attempts + 1\n",
    "            continue\n",
    "    else:\n",
    "        time.sleep(2)\n",
    "        clear_output()\n",
    "        print(\"You have reached the maximum number of attempts allowed.\")\n",
    "        email = input(\"Please enter your email address so that you can reset your password: \")\n",
    "        subject = \"Mentcare Patient Management System: Password Reset\"\n",
    "        body = \"\"\"We have received a request to reset your password for your account with Mental Health Care Center. For security reasons, we require you to confirm this request by clicking the link below:\n",
    "\n",
    "[Insert Password Reset Link Here]\n",
    "\n",
    "If you did not initiate this password reset request, please contact our support team immediately at mentalcare.cosc.3810@gmail.com to report the issue.\n",
    "\n",
    "Thank you for your attention to this matter.\n",
    "\n",
    "Best regards,\n",
    "Support\n",
    "Mental Health Care Center\"\"\"\n",
    "\n",
    "\n",
    "        msg = MIMEMultipart()\n",
    "        msg['From'] = 'mentalcare.cosc.3810@gmail.com'\n",
    "        msg['To'] = email\n",
    "        msg['Subject'] = subject\n",
    "\n",
    "        ##attach the email body as a plain text message\n",
    "        msg.attach(MIMEText(body, 'plain'))\n",
    "\n",
    "        smtp_server = smtplib.SMTP('smtp.gmail.com', 587) # Using the SMTP server of the email provider\n",
    "        smtp_server.starttls()\n",
    "        smtp_server.login('mentalcare.cosc.3810@gmail.com', 'password')  # Randomly created email that is going to be used\n",
    "        ##NOTE: This email address will be deleted after the completion of the project\n",
    "\n",
    "        smtp_server.send_message(msg)\n",
    "        smtp_server.quit()\n",
    "\n",
    "        print(\"Check you email\")\n",
    "        break\n",
    "\n",
    "\n",
    "##check if to many login attempts occured, if not menu begins\n",
    "if attempts < 3:\n",
    "\n",
    "##ADMIN CASE ✅========================================================================================================\n",
    "    if user.lower() == \"admin\":\n",
    "        \n",
    "        os.environ.get('TNS_ADMIN')\n",
    "        connection = oracledb.connect(\n",
    "            user='admin',\n",
    "            password='8&n:f6=$uMrFp+j>',\n",
    "            dsn = \"ms73dyswf309gljx_high\",\n",
    "            config_dir=\"./config\",\n",
    "            wallet_location=\"./config\",\n",
    "            wallet_password=\"6YW?}$.5H(N*+vj6\")\n",
    "\n",
    "        print(\"Setting up your menu...\")\n",
    "        time.sleep(2)\n",
    "        clear_output()\n",
    "\n",
    "        ##MENU~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n",
    "        while True:\n",
    "            print(\"MENU\")\n",
    "            string = \"1. Record information on appointment\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n2. Schedule appointment\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n3. Generate Report\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03) \n",
    "            string = \"\\n4. Quit\\n\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "\n",
    "            ##user input\n",
    "            choice = input()\n",
    "\n",
    "            ##Record information of appointment------------------------------------------------------------------------\n",
    "            if choice == \"1\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "\n",
    "                ##creating the inputs menu\n",
    "                print(\"Input Informarion of appointment:\")\n",
    "                \n",
    "                ##Gathering Patients username\n",
    "                PATIENTUNAME = input(\"Patient Username(Case Sensative): \")\n",
    "    \n",
    "                ##Gathering First Name\n",
    "                PATIENTFNAME = input(\"First name of Patient: \")                \n",
    "                PATIENTFNAME = PATIENTFNAME.title()\n",
    "                \n",
    "                ##Gathering Last Name\n",
    "                PATIENTLNAME = input(\"Last name of Patient: \")\n",
    "                PATIENTLNAME = PATIENTLNAME.title()\n",
    "                \n",
    "                ##Gathering Email\n",
    "                PATIENTEMAIL = input(\"Email of Patient: \")\n",
    "                PATIENTEMAIL = PATIENTEMAIL.title()\n",
    "                \n",
    "                ##Checking if Patient attended appointment\n",
    "                while True:\n",
    "                    PATIENTATTEND = input(\"Did the Patient attend?(y/n)\")\n",
    "                    if PATIENTATTEND.lower() == \"y\":\n",
    "                        PATIENTATTEND = \"Yes\"\n",
    "                        break\n",
    "                    elif PATIENTATTEND.lower() ==\"n\":\n",
    "                        PATIENTATTEND = \"No\"\n",
    "                        break\n",
    "                    else:\n",
    "                        print(\"Error: Invalid Input\")\n",
    "                        continue\n",
    "                        \n",
    "                ##Check if Patient was perscribed any medication\n",
    "                PATIENTMEDS = input(\"Perscribed Medication(If none press enter)\")\n",
    "                \n",
    "                ##Check Appointments Date\n",
    "                APPOINTMENTDATE = input(\"Date of Appointment(mm/dd/yyyy)\") \n",
    "                \n",
    "                ##Check Appointments Time\n",
    "                APPOINTMENTTIME = input(\"Time of Appointment (HH:MM in 24-hour clock)\")\n",
    "                \n",
    "                ##connection to DB\n",
    "                cursor = connection.cursor()\n",
    "    \n",
    "                ##try query to DB\n",
    "                try:\n",
    "                    query = f\"SELECT * FROM APPOINTMENTS WHERE PATIENTUNAME = '{PATIENTUNAME}' AND PATIENTFNAME = '{PATIENTFNAME}' AND PATIENTLNAME = '{PATIENTLNAME}' AND PATIENTEMAIL = '{PATIENTEMAIL}' AND PATIENTATTEND = '{PATIENTATTEND}' \\\n",
    "                        AND PATIENTMEDS = '{PATIENTMEDS}' AND APPOINTMENTDATE = '{APPOINTMENTDATE}' AND APPOINTMENTTIME ='{APPOINTMENTTIME}' \"\n",
    "                    response = cursor.execute(query).fetchall()\n",
    "                    if response:\n",
    "                        continue # SKIP IF ALREADY EXISTS IN DATABASE\n",
    "                    query = f\"INSERT INTO APPOINTMENTS VALUES ('{PATIENTUNAME}','{PATIENTFNAME}','{PATIENTLNAME}','{PATIENTEMAIL}', '{PATIENTATTEND}', '{PATIENTMEDS}', '{APPOINTMENTDATE}','{APPOINTMENTTIME}')\"\n",
    "                    cursor.execute(query)\n",
    "                    \n",
    "                ##exception = query invalid\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error updating Database:\", e)\n",
    "                    logging.warning(PATIENTUNAME, PATIENTFNAME, PATIENTLNAME, PATIENTEMAIL, PATIENTATTEND, PATIENTMEDS, APPOINTMENTDATE, APPOINTMENTTIME)\n",
    "\n",
    "                \n",
    "                try:\n",
    "                    if PATIENTATTEND == 'Yes':\n",
    "                        query = f\"SELECT * FROM PATIENTS WHERE USERNAME = '{PATIENTUNAME}'\"\n",
    "                        response = cursor.execute(query).fetchall()\n",
    "                        ##If it already exists in table, update patients w/ new table.\n",
    "                        if response:\n",
    "                            query = \"UPDATE PATIENTS p \\\n",
    "             SET \\\n",
    "                LASTATTEND = ( \\\n",
    "                    SELECT \\\n",
    "                        TO_CHAR(MAX(TO_DATE(a.APPOINTMENTDATE, 'MM/DD/YYYY')), 'MM/DD/YYYY') \\\n",
    "                    FROM \\\n",
    "                        Appointments a \\\n",
    "                    WHERE \\\n",
    "                        a.PATIENTUNAME = p.USERNAME \\\n",
    "                )\"\n",
    "                            cursor.execute(query)\n",
    "                    else:\n",
    "                        query = \"INSERT INTO Patients \\\n",
    "                        (USERNAME, NAME, EMAIL, MEDS, LASTATTEND) \\\n",
    "                        SELECT \\\n",
    "                            PATIENTUNAME, \\\n",
    "                            PATIENTFNAME || ' ' || PATIENTLNAME, \\\n",
    "                            PATIENTEMAIL, \\\n",
    "                            PATIENTMEDS, \\\n",
    "                            TO_CHAR(MAX(TO_DATE(APPOINTMENTDATE, 'MM/DD/YYYY')), 'MM/DD/YYYY') \\\n",
    "                        FROM \\\n",
    "                            Appointments \\\n",
    "                        WHERE NOT EXISTS ( \\\n",
    "                            SELECT 1 FROM Patients WHERE Patients.USERNAME = Appointments.PATIENTUNAME) \\\n",
    "                        GROUP BY \\\n",
    "                            PATIENTUNAME, PATIENTFNAME, PATIENTLNAME, PATIENTEMAIL, PATIENTMEDS\"\n",
    "                        cursor.execute(query)\n",
    "                ##exception = query invalid\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error updating Database:\", e)\n",
    "                ##commit the connection to DB    \n",
    "                \n",
    "                connection.commit()\n",
    "                \n",
    "                ##output and return to menu\n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()\n",
    "\n",
    "            ##Schedule Appointment-------------------------------------------------------------------------------------\n",
    "            elif choice == \"2\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "\n",
    "                ##creating the inputs menu\n",
    "                print(\"Input Informarion of appointment:\")\n",
    "                \n",
    "                ##Gathering Patients username\n",
    "                PATIENTUNAME = input(\"Patient Username(Case Sensative): \")\n",
    "                \n",
    "                ##Gathering First Name\n",
    "                PATIENTFNAME = input(\"First name of Patient: \")                \n",
    "                PATIENTFNAME = PATIENTFNAME.title()\n",
    "                \n",
    "                ##Gathering Last Name\n",
    "                PATIENTLNAME = input(\"Last name of Patient: \")\n",
    "                PATIENTLNAME = PATIENTLNAME.title()\n",
    "                \n",
    "                ##Gathering Email\n",
    "                PATIENTEMAIL = input(\"Email of Patient: \")\n",
    "                PATIENTEMAIL = PATIENTEMAIL.title()\n",
    "                \n",
    "                ##Checking if Patient attended appointment\n",
    "                while True:\n",
    "                    PATIENTATTEND = input(\"Did the Patient attend?(y/n)\")\n",
    "                    if PATIENTATTEND.lower() == \"y\":\n",
    "                        PATIENTATTEND = \"Yes\"\n",
    "                        break\n",
    "                    elif PATIENTATTEND.lower() ==\"n\":\n",
    "                        PATIENTATTEND = \"No\"\n",
    "                        break\n",
    "                    else:\n",
    "                        print(\"Error: Invalid Input\")\n",
    "                        continue\n",
    "                        \n",
    "                ##Check if Patient was perscribed any medication\n",
    "                PATIENTMEDS = input(\"Perscribed Medication(If none press enter)\")\n",
    "                \n",
    "                ##Check Appointments Date\n",
    "                APPOINTMENTDATE = input(\"Date of Appointment(mm/dd/yyyy)\") \n",
    "                \n",
    "                ##Check Appointments Time\n",
    "                APPOINTMENTTIME = input(\"Time of Appointment (HH:MM in 24-hour clock)\")\n",
    "                \n",
    "                \n",
    "                cursor = connection.cursor()\n",
    "\n",
    "                try:\n",
    "                    query = f\"SELECT * FROM APPOINTMENTS WHERE PATIENTUNAME = '{PATIENTUNAME}' AND PATIENTFNAME = '{PATIENTFNAME}' AND PATIENTLNAME = '{PATIENTLNAME}' AND PATIENTEMAIL = '{PATIENTEMAIL}' AND PATIENTATTEND = '{PATIENTATTEND}' \\\n",
    "                        AND PATIENTMEDS = '{PATIENTMEDS}' AND APPOINTMENTDATE = '{APPOINTMENTDATE}' AND APPOINTMENTTIME ='{APPOINTMENTTIME}' \"\n",
    "                    response = cursor.execute(query).fetchall()\n",
    "                    if response:\n",
    "                        continue # SKIP IF ALREADY EXISTS IN DATABASE\n",
    "                    query = f\"INSERT INTO APPOINTMENTS VALUES ('{PATIENTUNAME}','{PATIENTFNAME}','{PATIENTLNAME}','{PATIENTEMAIL}', '{PATIENTATTEND}', '{PATIENTMEDS}', '{APPOINTMENTDATE}','{APPOINTMENTTIME}')\"\n",
    "                    cursor.execute(query)\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error updating Database:\", e)\n",
    "                    logging.warning(PATIENTUNAME, PATIENTFNAME, PATIENTLNAME, PATIENTEMAIL, PATIENTATTEND, PATIENTMEDS, APPOINTMENTDATE, APPOINTMENTTIME)\n",
    "                \n",
    "                try:\n",
    "                    if PATIENTATTEND == 'Yes':\n",
    "                        query = f\"SELECT * FROM PATIENTS WHERE USERNAME = '{PATIENTUNAME}'\"\n",
    "                        response = cursor.execute(query).fetchall()\n",
    "                        ##If it already exists in table, update patients w/ new table.\n",
    "                        if response:\n",
    "                            query = \"UPDATE PATIENTS p \\\n",
    "             SET \\\n",
    "                LASTATTEND = ( \\\n",
    "                    SELECT \\\n",
    "                        TO_CHAR(MAX(TO_DATE(a.APPOINTMENTDATE, 'MM/DD/YYYY')), 'MM/DD/YYYY') \\\n",
    "                    FROM \\\n",
    "                        Appointments a \\\n",
    "                    WHERE \\\n",
    "                        a.PATIENTUNAME = p.USERNAME \\\n",
    "                )\"\n",
    "                            cursor.execute(query)\n",
    "                    else:\n",
    "                        query = \"INSERT INTO Patients \\\n",
    "                        (USERNAME, NAME, EMAIL, MEDS, LASTATTEND) \\\n",
    "                        SELECT \\\n",
    "                            PATIENTUNAME, \\\n",
    "                            PATIENTFNAME || ' ' || PATIENTLNAME, \\\n",
    "                            PATIENTEMAIL, \\\n",
    "                            PATIENTMEDS, \\\n",
    "                            TO_CHAR(MAX(TO_DATE(APPOINTMENTDATE, 'MM/DD/YYYY')), 'MM/DD/YYYY') \\\n",
    "                        FROM \\\n",
    "                            Appointments \\\n",
    "                        WHERE NOT EXISTS ( \\\n",
    "                            SELECT 1 FROM Patients WHERE Patients.USERNAME = Appointments.PATIENTUNAME) \\\n",
    "                        GROUP BY \\\n",
    "                            PATIENTUNAME, PATIENTFNAME, PATIENTLNAME, PATIENTEMAIL, PATIENTMEDS\"\n",
    "                        cursor.execute(query)\n",
    "                ##exception = query invalid\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error updating Database:\", e)\n",
    "                ##commit the connection to DB    \n",
    "                \n",
    "                connection.commit()\n",
    "                \n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()\n",
    "\n",
    "            ##Generate a report----------------------------------------------------------------------------------------\n",
    "            elif choice == \"3\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                \n",
    "                print(\"Report Generating...\")\n",
    "                \n",
    "                rs = cursor.execute(\"SELECT * FROM APPOINTMENTS\")\n",
    "                df = pd.DataFrame(rs.fetchall())\n",
    "                df.columns = ['Patients Username','Patients First Name' , 'Patients Last Name' , 'Patients Email' , 'Attendance' , 'Perscriptions' , 'Appointment Date', 'Appointment Time']\n",
    "                \n",
    "                filename = f'adminreport_{datetime.datetime.now().strftime(\"%H_%M_%m_%d_%Y\")}.csv'\n",
    "                df.to_csv(filename, index= True)\n",
    "                \n",
    "                time.sleep(1)\n",
    "                print(\"Report Generated. See exported CSV.\")\n",
    "                time.sleep(1)\n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()\n",
    "\n",
    "            ##Quit-----------------------------------------------------------------------------------------------------\n",
    "            elif choice == \"4\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                print(\"Logging you out...\")\n",
    "                time.sleep(2)\n",
    "                print(\"Logged out. Have a great day\")\n",
    "                break\n",
    "\n",
    "            ##Choice Not given-----------------------------------------------------------------------------------------\n",
    "            else:\n",
    "                print(\"ERROR: Invalid selection. Please select an option\")\n",
    "                time.sleep(1)\n",
    "                clear_output() \n",
    "\n",
    "##RECORDS MANAGER CASE ✅==============================================================================================\n",
    "    elif user.lower() == \"records_manager\":\n",
    "        \n",
    "        os.environ.get('TNS_ADMIN')\n",
    "        connection = oracledb.connect(\n",
    "            user='admin',\n",
    "            password='8&n:f6=$uMrFp+j>',\n",
    "            dsn = \"ms73dyswf309gljx_high\",\n",
    "            config_dir=\"./config\",\n",
    "            wallet_location=\"./config\",\n",
    "            wallet_password=\"6YW?}$.5H(N*+vj6\")        \n",
    "        \n",
    "        print(\"Setting up your menu...\")\n",
    "        time.sleep(2)\n",
    "        clear_output()\n",
    "        \n",
    "        ##MENU~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n",
    "        while True:\n",
    "            print(\"MENU\")\n",
    "            string = \"1. Create new users\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n2. Download Patient Information\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n3. Quit\\n\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "\n",
    "            ##user input\n",
    "            choice = input()\n",
    "            \n",
    "            ##adding new user to db------------------------------------------------------------------------------------\n",
    "            if choice == \"1\":\n",
    "                \n",
    "                username = input(\"Patient's username: \")\n",
    "                password = getpass.getpass(\"Patients Password:\")\n",
    "\n",
    "                cursor = connection.cursor()\n",
    "\n",
    "                try:\n",
    "                    query = f'CREATE USER {username} IDENTIFIED BY \"{password}\"'\n",
    "                    cursor.execute(query)\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error adding user to Database:\", e)\n",
    "                    break\n",
    "\n",
    "                    \n",
    "                print(\"User has been added to the Database\")\n",
    "                print(\"Please provide more information about the patient\")\n",
    "               \n",
    "                ##gather name and capitalize\n",
    "                name = input(\"Patient Name\")\n",
    "                name = name.title()\n",
    "                \n",
    "                ##gather patient address\n",
    "                address = input(\"Patients Address\")\n",
    "                \n",
    "                ##gather patient email\n",
    "                email = input(\"Patients Email Address\")\n",
    "                \n",
    "                ##gather the patients medications if applicable\n",
    "                meds = input(\"Patients current medications(press enter if none apply)\")\n",
    "                \n",
    "                ##assuming that the first appointment is the date that user is being input\n",
    "                ##so I will be hard coding in the date that the user is input in the system\n",
    "                lastattend = date.today().strftime('%m/%d/%Y') \n",
    "                \n",
    "                cursor = connection.cursor()\n",
    "                \n",
    "                ##attempt to insert values into the db\n",
    "                try:\n",
    "                    query = f\"INSERT INTO PATIENTS VALUES ('{username}','{name}','{address}','{email}','{meds}','{lastattend}')\"\n",
    "                    cursor.execute(query)\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error inserting Patient:\", e)\n",
    "                \n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()\n",
    "                \n",
    "            ##Download Patient Info------------------------------------------------------------------------------------\n",
    "            elif choice == \"2\":\n",
    "                \n",
    "                username = input(\"Patient's username: \")\n",
    "                time.sleep(1)\n",
    "                print(\"Downloading Patient Information. One moment Please\")\n",
    "                time.sleep(3)\n",
    "                \n",
    "                cursor = connection.cursor()\n",
    "\n",
    "                try:\n",
    "                    query = f\"SELECT * FROM PATIENTS WHERE USERNAME = '{username}'\"\n",
    "                    rs = cursor.execute(query)\n",
    "                    df = pd.DataFrame(rs.fetchall())\n",
    "                    df.columns = ['Patient Username','Patients Name' , 'Patients ADDRESS' , 'Patients Email' , 'Patients Medications' , 'Last Attendance Date']\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error updating Database:\", e)\n",
    "\n",
    "                connection.commit()\n",
    "                \n",
    "                filename = f'patientlookup_{username}_{datetime.datetime.now().strftime(\"%H_%M_%m_%d_%Y\")}.csv'\n",
    "                df.to_csv(filename, index= True)\n",
    "                time.sleep(1)\n",
    "                print(\"CSV exported.\")\n",
    "                time.sleep(1)\n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()\n",
    "            ##Quit-----------------------------------------------------------------------------------------------------\n",
    "            elif choice == \"3\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                print(\"Logging you out...\")\n",
    "                time.sleep(2)\n",
    "                print(\"Logged out. Have a great day\")\n",
    "                break\n",
    "        \n",
    "            ##choice not given-----------------------------------------------------------------------------------------\n",
    "            else:\n",
    "                print(\"ERROR: Invalid selection. Please select an option\")\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "\n",
    "##CLINICAL STAFF CASE ✅===============================================================================================\n",
    "    elif user.lower() == \"clinicalstaff\":\n",
    "        \n",
    "        os.environ.get('TNS_ADMIN')\n",
    "        connection = oracledb.connect(\n",
    "            user='admin',\n",
    "            password='8&n:f6=$uMrFp+j>',\n",
    "            dsn = \"ms73dyswf309gljx_high\",\n",
    "            config_dir=\"./config\",\n",
    "            wallet_location=\"./config\",\n",
    "            wallet_password=\"6YW?}$.5H(N*+vj6\")\n",
    "        \n",
    "        print(\"Setting up your menu...\")\n",
    "        time.sleep(2)\n",
    "        clear_output()\n",
    "        \n",
    "        ##MENU~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n",
    "        while True:\n",
    "            print(\"MENU\")\n",
    "            string = \"1. Look up Patient \"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n2. Modify Patient Information\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n3. Quit\\n\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "\n",
    "            ##Selecting what user wants to do  \n",
    "            choice = input()\n",
    "            \n",
    "            ##Lookup Patient Info--------------------------------------------------------------------------------------\n",
    "            if choice == \"1\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                \n",
    "                ##Gather Username\n",
    "                USERNAME = input(\"Patients Username(Case Sensative): \")\n",
    "                \n",
    "                print(\"Searching the Database, one moment please\")\n",
    "                time.sleep(3)\n",
    "                clear_output()\n",
    "\n",
    "                cursor = connection.cursor()\n",
    "\n",
    "                try:\n",
    "                    query = f\"SELECT * FROM PATIENTS WHERE USERNAME = '{USERNAME}'\"\n",
    "                    rs = cursor.execute(query)\n",
    "                    df = pd.DataFrame(rs.fetchall())\n",
    "                    df.columns = ['Patient Username','Patients Name' , 'Patients ADDRESS' , 'Patients Email' , 'Patients Medications' , 'Last Attendance Date']\n",
    "                    display(df)\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error updating Database:\", e)\n",
    "\n",
    "                connection.commit()\n",
    "                \n",
    "                string = \"\\n1. Return to Menu \"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                string = \"\\n2. Export as csv\"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                string = \"\\n3. Exit and Logout\\n\"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                    \n",
    "                ##Selecting what user wants to do  \n",
    "                choice = input()\n",
    "                \n",
    "                ##return to the menu-----------------------------------------------------------------------------------\n",
    "                if choice == '1':\n",
    "                    print(\"Returning to Menu\")\n",
    "                    time.sleep(2)\n",
    "                    clear_output()\n",
    "                    \n",
    "                ##exporting the data-----------------------------------------------------------------------------------\n",
    "                elif choice == '2':\n",
    "                    filename = f'patientlookup_{datetime.datetime.now().strftime(\"%H_%M_%m_%d_%Y\")}.csv'\n",
    "                    df.to_csv(filename, index= True)\n",
    "                    time.sleep(1)\n",
    "                    print(\"CSV exported.\")\n",
    "                    time.sleep(1)\n",
    "                    print(\"Returning to Menu\")\n",
    "                    time.sleep(2)\n",
    "                    clear_output()\n",
    "                    \n",
    "                ##quit-------------------------------------------------------------------------------------------------\n",
    "                elif choice == '3':\n",
    "                    ##clearing the menu\n",
    "                    time.sleep(1)\n",
    "                    clear_output()\n",
    "                    print(\"Logging you out...\")\n",
    "                    time.sleep(2)\n",
    "                    print(\"Logged out. Have a great day\")\n",
    "                    break   \n",
    "                    \n",
    "                ##choice not given-------------------------------------------------------------------------------------\n",
    "                else:\n",
    "                    print(\"ERROR: Invalid selection. Please select an option\")\n",
    "                    time.sleep(1)\n",
    "                    clear_output()\n",
    "\n",
    "                    \n",
    "            ##Modify Patient Information-------------------------------------------------------------------------------\n",
    "            elif choice == \"2\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                \n",
    "                USERNAME = input(\"Please provide the Username(case sensitive) of the patient you wish to view\")\n",
    "                print(\"One moment while the database is queried\")\n",
    "\n",
    "                try:\n",
    "                    query = f\"SELECT * FROM PATIENTS WHERE USERNAME = '{USERNAME}'\"\n",
    "                    rs = cursor.execute(query)\n",
    "                    df = pd.DataFrame(rs.fetchall())\n",
    "                    df.columns = ['Patient Username','Patients Name' , 'Patients ADDRESS' , 'Patients Email' , 'Patients Medications' , 'Last Attendance Date']\n",
    "                    display(df)\n",
    "                except Exception as e:\n",
    "                    logging.warning(\"Error locating in Database:\", e)\n",
    "\n",
    "                connection.commit()\n",
    "                \n",
    "                ##can only update patients by adding appointments, so I'll give option \n",
    "                ##to just return to menu incase of accidental selection\n",
    "                string = \"\\n1. Update Email Address\"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                string = \"\\n2. Update Address\"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                string = \"\\n3. Update Medications\"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                string = \"\\n4. Return to Menu\"\n",
    "                for char in string:\n",
    "                    print(char, end='')\n",
    "                    time.sleep(.03)\n",
    "                    \n",
    "                ##Selecting what user wants to do  \n",
    "                choice = input()\n",
    "                \n",
    "                ##Update Email-----------------------------------------------------------------------------------------\n",
    "                if choice == '1':\n",
    "                    email = input(\"Enter the new Email Address: \")           \n",
    "                    email = email.title()\n",
    "                    \n",
    "                    lastattend = input(\"Date of Appointment(mm/dd/yyyy)\")\n",
    "                    \n",
    "                    try:\n",
    "                        query = f\"UPDATE PATIENTS SET EMAIL = '{email}', LASTATTEND = '{lastattend}' WHERE USERNAME = '{USERNAME}'\"\n",
    "                        cursor.execute(query)\n",
    "                    except Exception as e:\n",
    "                        logging.warning(\"Error Updating User: \", e)\n",
    "                    \n",
    "                    connection.commit()\n",
    "\n",
    "                    time.sleep(2)\n",
    "                    clear_output()\n",
    "                    \n",
    "                ##Update Address---------------------------------------------------------------------------------------\n",
    "                elif choice == '2':\n",
    "                    address = input(\"Enter the new Address: \")           \n",
    "                    \n",
    "                    lastattend = input(\"Date of Appointment(mm/dd/yyyy)\")\n",
    "                    \n",
    "                    try:\n",
    "                        query = f\"UPDATE PATIENTS SET ADDRESS = '{address}', LASTATTEND = '{lastattend}' WHERE USERNAME = '{USERNAME}'\"\n",
    "                        cursor.execute(query)\n",
    "                    except Exception as e:\n",
    "                        logging.warning(\"Error Updating User: \", e)\n",
    "                    \n",
    "                    connection.commit()\n",
    "                    \n",
    "                    time.sleep(2)\n",
    "                    clear_output()\n",
    "                \n",
    "                ##update medications-----------------------------------------------------------------------------------\n",
    "                elif choice == '3':\n",
    "                    meds = input(\"Perscribed Medication(If none press enter)\")           \n",
    "                    \n",
    "                    lastattend = input(\"Date of Appointment(mm/dd/yyyy)\")\n",
    "                    \n",
    "                    try:\n",
    "                        query = f\"UPDATE PATIENTS SET MEDS = '{meds}', LASTATTEND = '{lastattend}' WHERE USERNAME = '{USERNAME}'\"\n",
    "                        cursor.execute(query)\n",
    "                    except Exception as e:\n",
    "                        logging.warning(\"Error Updating User: \", e)\n",
    "\n",
    "                    connection.commit()\n",
    "\n",
    "                    time.sleep(2)\n",
    "                    clear_output()\n",
    "                \n",
    "                ##return to menu---------------------------------------------------------------------------------------\n",
    "                elif choice == '4':\n",
    "                    print(\"Returning to Menu\")\n",
    "                    time.sleep(2)\n",
    "                    clear_output()\n",
    "                    \n",
    "                ##choice not given-------------------------------------------------------------------------------------\n",
    "                else:\n",
    "                    print(\"ERROR: Invalid selection. Please select an option\")\n",
    "                    time.sleep(1)\n",
    "                    clear_output()\n",
    "                \n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()   \n",
    "                \n",
    "            ##Quit-----------------------------------------------------------------------------------------------------\n",
    "            elif choice == \"3\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                print(\"Logging you out...\")\n",
    "                time.sleep(2)\n",
    "                print(\"Logged out. Have a great day\")\n",
    "                break\n",
    "        \n",
    "            ##choice not given-----------------------------------------------------------------------------------------\n",
    "            else:\n",
    "                print(\"ERROR: Invalid selection. Please select an option\")\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "\n",
    "\n",
    "##USERS CASE ✅========================================================================================================       \n",
    "    else:\n",
    "        os.environ.get('TNS_ADMIN')\n",
    "        connection = oracledb.connect(\n",
    "            user='admin',\n",
    "            password='8&n:f6=$uMrFp+j>',\n",
    "            dsn = \"ms73dyswf309gljx_high\",\n",
    "            config_dir=\"./config\",\n",
    "            wallet_location=\"./config\",\n",
    "            wallet_password=\"6YW?}$.5H(N*+vj6\")  \n",
    "        \n",
    "        ##MENU FOR RECORDS MANAGERS\n",
    "        print(\"Setting up your menu...\")\n",
    "        time.sleep(2)\n",
    "        clear_output()\n",
    "        \n",
    "        ##printing out Menu\n",
    "        while True:\n",
    "            print(\"MENU\")\n",
    "            string = \"1. View Appointment Records\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n2. Download Appointment Records\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "            string = \"\\n3. View Your Information\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03) \n",
    "            string = \"\\n4. Quit\\n\"\n",
    "            for char in string:\n",
    "                print(char, end='')\n",
    "                time.sleep(.03)\n",
    "\n",
    "            ##Selecting what user wants to do  \n",
    "            choice = input()\n",
    "            \n",
    "            ##viewing data from Appointments---------------------------------------------------------------------------\n",
    "            if choice == \"1\":\n",
    "                cursor = connection.cursor()\n",
    "                rs = cursor.execute(f\"SELECT * FROM APPOINTMENTS WHERE PATIENTUNAME = '{user}'\")\n",
    "                df = pd.DataFrame(rs.fetchall())\n",
    "                df.columns = ['Patients Username','Patients First Name' , 'Patients Last Name' , 'Patients Email' , 'Attendance' , 'Perscriptions' , 'Appointment Date', 'Appointment Time']\n",
    "                display(df)\n",
    "                choice = input(\"Press Enter to go back to Menu\")\n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output() \n",
    "                \n",
    "            ##exporting data from appointments-------------------------------------------------------------------------\n",
    "            elif choice == \"2\":\n",
    "                cursor = connection.cursor()\n",
    "                rs = cursor.execute(f\"SELECT * FROM APPOINTMENTS WHERE PATIENTUNAME = '{user}'\")\n",
    "                df = pd.DataFrame(rs.fetchall())\n",
    "                df.columns = ['Patients Username','Patients First Name' , 'Patients Last Name' , 'Patients Email' , 'Attendance' , 'Perscriptions' , 'Appointment Date', 'Appointment Time']\n",
    "\n",
    "                filename = f'{user}_data_{datetime.datetime.now().strftime(\"%H_%M_%m_%d_%Y\")}.csv'\n",
    "                df.to_csv(filename, index= True)\n",
    "                print(\"See downloads for file\")\n",
    "                time.sleep(2)\n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()                \n",
    "            ##view patient information---------------------------------------------------------------------------------\n",
    "            elif choice == \"3\":\n",
    "                cursor = connection.cursor()\n",
    "                rs = cursor.execute(f\"SELECT * FROM PATIENTS WHERE USERNAME = '{user}'\")\n",
    "                df = pd.DataFrame(rs.fetchall())\n",
    "                df.columns = ['Username','Name' , 'Email' , 'Address' , 'Medications' , 'Most Recent Appointment']\n",
    "                display(df)\n",
    "                choice = input(\"Press Enter to go back to Menu\")\n",
    "                print(\"Returning to Menu\")\n",
    "                time.sleep(2)\n",
    "                clear_output()\n",
    "            ##Quit-----------------------------------------------------------------------------------------------------\n",
    "            elif choice == \"4\":\n",
    "                ##clearing the menu\n",
    "                time.sleep(1)\n",
    "                clear_output()\n",
    "                print(\"Logging you out...\")\n",
    "                time.sleep(2)\n",
    "                print(\"Logged out. Have a great day\")\n",
    "                break\n",
    "            ##choice not given-----------------------------------------------------------------------------------------\n",
    "            else:\n",
    "                print(\"ERROR: Invalid selection. Please select an option\")\n",
    "                time.sleep(1)\n",
    "                clear_output()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}