Untitled1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "DPI-1047: 64-bit Oracle Client library cannot be loaded: \"The specified module could not be found\". See https://oracle.github.io/odpi/doc/installation.html#windows for help",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-2-1a5d9a634abf>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mdatetime\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mdatetime\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mdate\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mcon\u001b[0m\u001b[1;33m=\u001b[0m \u001b[0mcx_Oracle\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'PROJECT/123@xe'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      5\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m=\u001b[0m \u001b[0mcon\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: DPI-1047: 64-bit Oracle Client library cannot be loaded: \"The specified module could not be found\". See https://oracle.github.io/odpi/doc/installation.html#windows for help"
     ]
    }
   ],
   "source": [
    "import cx_Oracle\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "con= cx_Oracle.connect('PROJECT/123@xe')\n",
    "cur= con.cursor()\n",
    "\n",
    "def mainmenu():\n",
    "    print(\"MAIN MENU\")\n",
    "    print(\"1. Sign Up\")\n",
    "    print(\"2. Sign In\")\n",
    "    print(\"3. Admin Sign In\")\n",
    "    print(\"4. Exit\")\n",
    "    x=input(\"Enter Choice:\")\n",
    "    if x=='1':\n",
    "        signup()\n",
    "    elif x=='2':\n",
    "        signin()\n",
    "    elif x=='3':\n",
    "        adminsignin()\n",
    "    elif x=='4':\n",
    "        exit()\n",
    "    else:\n",
    "        print(\"Enter valid option\")\n",
    "        mainmenu()\n",
    "\n",
    "def signup():\n",
    "    print(\"ENTER INFORMATION:\")\n",
    "    fname=input(\"Firstname:\")\n",
    "    lname=input(\"Lastname:\")\n",
    "    name=\"%s %s\" %(fname,lname)\n",
    "    print(\"Address:\")\n",
    "    add1=input(\"Line1:\")\n",
    "    add2=input(\"Line2:\")\n",
    "    add3=input(\"city:\")\n",
    "    add4=input(\"State:\")\n",
    "    add5=x1()\n",
    "    add=\"%s %s , %s , %s, %d\" %(add1,add2,add3,add4,add5)\n",
    "    type1=x2()\n",
    "    pass1=x3()\n",
    "    bal=x4(type1)\n",
    "    cur.execute('select c_id.nextval,a_id.nextval from dual')\n",
    "    con.commit()\n",
    "    res= cur.fetchall()\n",
    "    for r in res:\n",
    "        id1=\"%s%d\" %('CID',r[0])\n",
    "        id2=\"%s%d\" %('ACN00',r[1])\n",
    "    cur.execute('INSERT INTO CUSTOMER VALUES(:1,:2,:3,:4,:5,:6,:7)',(id1,id2,name,add,type1,bal,pass1))\n",
    "    con.commit()\n",
    "    print(\"New Customer Id created.\")\n",
    "    print(\"Customer Id:\"+id1)\n",
    "    print(\"Account no.\"+id2)\n",
    "    mainmenu()\n",
    "    \n",
    "def signin():\n",
    "    x=0\n",
    "    i=1\n",
    "    list1=[]\n",
    "    while x<3:\n",
    "        cid=input(\"Customer Id:\")\n",
    "        p=input(\"Password:\")\n",
    "        cur.execute('SELECT * from CUSTOMER WHERE cus_id=:1 and password=:2',(cid,p))\n",
    "        res=cur.fetchall()\n",
    "        con.commit()\n",
    "        if res:\n",
    "            for r in res:\n",
    "                mul=\"%d %s %s\" %(i,'.',r[1])\n",
    "                list1.append(r[1])\n",
    "                print(mul)\n",
    "                i+=1\n",
    "            if i>1:\n",
    "                x=int(input(\"You have multiple account. please enter choice given above account no. for transaction(1,2,...):\"))\n",
    "                submenu(cid,list1[x-1],p)\n",
    "            else:\n",
    "                submenu(cid,r[1],p)\n",
    "        else:\n",
    "            print(\"Invalid USER-ID/Password!\")\n",
    "            x+=1\n",
    "    if x==3 :\n",
    "        print(\"More than 3 attempts are not allowed\")\n",
    "        mainmenu()\n",
    "        \n",
    "def adminsignin():\n",
    "    x=0\n",
    "    while x<3:\n",
    "        admin=input(\"Admin Id:\")\n",
    "        p=input(\"Password:\")\n",
    "        cur.execute('SELECT * from admin1 WHERE adminid=:1 and password=:2',(admin,p))\n",
    "        r=cur.fetchall()\n",
    "        con.commit()\n",
    "        print(r)\n",
    "        if r:\n",
    "            submenu2(admin,p)\n",
    "        else:\n",
    "            print(\"Invalid USER-ID/Password!\")\n",
    "            x+=1\n",
    "    if x==3:\n",
    "        print(\"More than 3 attempts are not allowed\")\n",
    "        mainmenu()\n",
    "\n",
    "def x1():\n",
    "    add5=int(input(\"Pincode:\"))\n",
    "    if add5 >= 100000 and add5 <= 999999:\n",
    "        return add5\n",
    "    else:\n",
    "        print(\"Enter 6 digit number:\")\n",
    "        x1()\n",
    "\n",
    "def x2():\n",
    "    type1=input(\"Type of account(SA,CA):\")\n",
    "    if type1!=\"SA\" and type1!=\"CA\":\n",
    "        print(\"Invalid entry\")\n",
    "        x2()\n",
    "    else:\n",
    "        return type1\n",
    "\n",
    "def x3():\n",
    "    pass1=input(\"Password(8-Digit):\")\n",
    "    t=len(pass1)\n",
    "    if(t>=8):\n",
    "        return pass1\n",
    "    else:\n",
    "        print(\"invalid password(too short)\")\n",
    "        x3()\n",
    "\n",
    "def x4(type1):\n",
    "    if type1==\"CA\":\n",
    "        bal=5000\n",
    "    else:\n",
    "        bal=0\n",
    "    return bal\n",
    "\n",
    "def submenu(cid,acc,p):\n",
    "    print(\"SUB MENU\")\n",
    "    print(\"1. Address Change\")\n",
    "    print(\"2. Open Account\")\n",
    "    print(\"3. Money Deposit\")\n",
    "    print(\"4. Money Withdrawal\")\n",
    "    print(\"5. Print Statement\")\n",
    "    print(\"6. Transfer Money\")\n",
    "    print(\"7. Account Closure\")\n",
    "    print(\"0. Customer Logout\")\n",
    "    x=int(input(\"Enter Choice:\"))\n",
    "    if x==1:\n",
    "        changeaddress(cid)\n",
    "        print(\"Address is changed\")\n",
    "        submenu(cid,acc,p)\n",
    "    elif x==2:\n",
    "        openacc(cid,p)\n",
    "    elif x==3:\n",
    "        ammount=int(input(\"Enter the ammount:\"))\n",
    "        deposit(cid,acc,ammount)\n",
    "        submenu(cid,acc,p)\n",
    "    elif x==4:\n",
    "        ammount=int(input(\"Enter the ammount:\"))\n",
    "        withdrawal(cid,acc,ammount)\n",
    "        submenu(cid,acc,p)\n",
    "    elif x==5:\n",
    "        datef=input(\"mm/dd/yyyy\")\n",
    "        datet=input(\"mm/dd/yyyy\")\n",
    "        print(datef,datet)\n",
    "        printstatement(acc,datef,datet)\n",
    "        submenu(cid,acc,p)\n",
    "    elif x==6:\n",
    "        acc2=input(\"Enter account no. to transfer money: \")\n",
    "        ammount=int(input(\"Enter the ammount:\"))\n",
    "        transfer(cid,acc,acc2,ammount)\n",
    "        submenu(cid,acc,p)\n",
    "    elif x==7:\n",
    "        accountclose(acc,p)\n",
    "        print(\"Your account is closed\")\n",
    "        mainmenu()\n",
    "    elif x==0:\n",
    "        mainmenu()\n",
    "    else:\n",
    "        print(\"Choose correct option\")\n",
    "        submenu(cid,acc,p)\n",
    "\n",
    "def changeaddress(cid):\n",
    "    add1=input(\"Line1:\")\n",
    "    add2=input(\"Line2:\")\n",
    "    add3=input(\"city:\")\n",
    "    add4=input(\"State:\")\n",
    "    add5=x1()\n",
    "    add=\"%s %s , %s , %s %d\" %(add1,add2,add3,add4,add5)\n",
    "    cur.execute('UPDATE CUSTOMER set address=:1 where cus_id=:2',(add,cid))\n",
    "    print(\"Address changed...\")\n",
    "    con.commit()\n",
    "\n",
    "def openacc(cip,p):\n",
    "    print(\"1. Open SA\")\n",
    "    print(\"1. Open CA\")\n",
    "    print(\"1. Open FD\")\n",
    "    type1=x2()\n",
    "    bal=x4(type1)\n",
    "    cur.execute('SELECT A_ID.NEXTVAL FROM DUAL')\n",
    "    con.commit()\n",
    "    res=cur.fetchall()\n",
    "    for r in res:\n",
    "        id2=\"%s%d\" %('ACN00',r[0])\n",
    "    cur.execute('SELECT * FROM CUSTOMER WHERE cus_id=:1 and password=:2',(cip,p))\n",
    "    con.commit()\n",
    "    res=cur.fetchall()\n",
    "    for r in res:\n",
    "        cur.execute('INSERT INTO CUSTOMER VALUES(:1,:2,:3,:4,:5,:6,:7)',(cip,id2,r[2],r[3],type1,bal,p))\n",
    "        con.commit()\n",
    "    print(\"New Customer Id created.\")\n",
    "    print(\"Customer Id:\"+cip)\n",
    "    print(\"Account no.\"+id2)\n",
    "    submenu(cip,id2,p)\n",
    "\n",
    "def deposit(cid,acc,ammount):  \n",
    "    today= date.today()\n",
    "    p=\"%s %d\" %('deposit',ammount)\n",
    "    cur.execute('SELECT balance FROM CUSTOMER WHERE acc_no=:1 and balance>=:2 or balance<:2',(acc,ammount))\n",
    "    con.commit()\n",
    "    res=cur.fetchall()\n",
    "    for r in res:\n",
    "        cur.execute('INSERT INTO RECORD VALUES(:1,:2,:3,:4,:5)',(cid,acc,today,p,r[0]+ammount))\n",
    "        break\n",
    "    cur.execute('UPDATE CUSTOMER SET balance=balance+:1 where acc_no=:2' ,(ammount,acc))\n",
    "    print(\"Deposit successful\")\n",
    "    con.commit()\n",
    "    \n",
    "def withdrawal(cid,acc,ammount):\n",
    "    today= date.today()\n",
    "    p=\"%s %d\" %('withdrawal',ammount)\n",
    "    cur.execute('SELECT balance,type FROM CUSTOMER WHERE acc_no=:1 and balance>=:2',(acc,ammount))\n",
    "    con.commit()\n",
    "    res=cur.fetchall()\n",
    "    if res:\n",
    "        for r in res:\n",
    "            ty=r[1]\n",
    "            am=r[0]-ammount\n",
    "        if (ty=='CA' and am>=5000) or ty!='CA':\n",
    "            cur.execute('UPDATE CUSTOMER SET balance=balance-:1 where acc_no=:2 and balance>:1' ,(ammount,acc))\n",
    "            cur.execute('INSERT INTO RECORD VALUES(:1,:2,:3,:4,:5)',(cid,acc,today,p,am))\n",
    "            print(\"Withdrawal successful\")\n",
    "            con.commit()\n",
    "        else:\n",
    "            print(\"min balance is not sufficient\")\n",
    "    else:\n",
    "        print(\"insufficient amount..\")\n",
    "        submenu(acc,p)\n",
    "\n",
    "def transfer(cid,acc,acc2,ammount):\n",
    "    today= date.today()\n",
    "    p=\"%s %d %s %s\" %('Tranfer',ammount,'to',acc2)\n",
    "    cur.execute('SELECT balance,type FROM CUSTOMER WHERE acc_no=:1 and balance>=:2',(acc,ammount))\n",
    "    con.commit()\n",
    "    res=cur.fetchall()\n",
    "    if res:\n",
    "        for r in res:\n",
    "            ty=r[1]\n",
    "            am=r[0]-ammount\n",
    "        if (ty=='CA' and am>=5000) or ty!='CA':\n",
    "            cur.execute('UPDATE CUSTOMER SET balance=balance-:1 where acc_no=:2 and balance>:1' ,(ammount,acc))\n",
    "            cur.execute('INSERT INTO RECORD VALUES(:1,:2,:3,:4,:5)',(cid,acc,today,p,am))\n",
    "            print(\"Withdrawal successful\")\n",
    "            con.commit()\n",
    "            cur.execute('SELECT balance,cus_id FROM CUSTOMER WHERE acc_no=:1 and balance>=:2 or balance<:2',(acc2,ammount))\n",
    "            con.commit()\n",
    "            res=cur.fetchall()\n",
    "            p=\"%s %d %s %s\" %('deposit',ammount,'by',acc)\n",
    "            for r in res:\n",
    "                cur.execute('INSERT INTO RECORD VALUES(:1,:2,:3,:4,:5)',(r[1],acc2,today,p,r[0]+ammount))\n",
    "                break\n",
    "            cur.execute('UPDATE CUSTOMER SET balance=balance+:1 where acc_no=:2' ,(ammount,acc2))\n",
    "            print(\"Deposit successful\")\n",
    "            con.commit()\n",
    "        else:\n",
    "            print(\"min balance is not sufficient\")\n",
    "    else:\n",
    "        print(\"insufficient amount..\")\n",
    "        print(\"Transaction failed\")\n",
    "    \n",
    "def printstatement(acc,datefrom,dateto):\n",
    "    d1=datetime.strptime(datefrom, '%m/%d/%Y')\n",
    "    d2=datetime.strptime(dateto, '%m/%d/%Y')\n",
    "    if d1>d2 :\n",
    "        print(\"invalid date\")\n",
    "    else:\n",
    "        cur.execute('SELECT * FROM RECORD WHERE (acc_no=:1 AND (activity_date>=:2 AND activity_date<=:3))',(acc,d1,d2))\n",
    "        con.commit()\n",
    "        res=cur.fetchall()\n",
    "        if res:\n",
    "            for r in res:\n",
    "                print(r)\n",
    "        else:\n",
    "            print(\"No Record Found\")\n",
    "            \n",
    "def accountclose(acc,p):\n",
    "    today= date.today()\n",
    "    cur.execute('SELECT cus_id,acc_no,balance,address FROM CUSTOMER WHERE acc_no=:1 AND password=:2',(acc,p))\n",
    "    con.commit()\n",
    "    res=cur.fetchall()\n",
    "    for r in res:\n",
    "        cur.execute('insert into history values(:1,:2,:3,:4,:5)',(r[0],r[1],r[2],today,r[3]))\n",
    "        con.commit()\n",
    "        break\n",
    "    cur.execute('DELETE CUSTOMER WHERE acc_no=:1 and password=:2',(acc,p))\n",
    "    con.commit()\n",
    "    msg=\"%s %s %s\" %(r[2],'amount is send to',r[3])\n",
    "    print(msg)\n",
    "\n",
    "def submenu2(admin,p):\n",
    "    print(\"1. Print closed account history\")\n",
    "    print(\"2. Admin logout\")\n",
    "    x=int(input(\"Enter choice\"))\n",
    "    if x==1:\n",
    "        cur.execute(\"SELECT * FROM HISTORY\")\n",
    "        con.commit()\n",
    "        res=cur.fetchall()\n",
    "        if res:\n",
    "            for r in res:\n",
    "                print(r)\n",
    "            submenu2(admin,p)\n",
    "        else:\n",
    "            print(\"History not found\")\n",
    "            submenu2(admin,p)\n",
    "    elif x==2:\n",
    "        mainmenu()\n",
    "    else:\n",
    "        print(\"Invalid choice\")\n",
    "        submenu2(admin,p)\n",
    "\n",
    "mainmenu()\n",
    "con.close()"
   ]
  },
  {
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}