def addBook():
"""add book to database with name author and publush date
Returns:
template -- addBook page template with appropriate error message
"""
if 'username' in session:
form = NewBook()
items = dbCon().selectQName(
"""select bookid,title,author,publisheddate,
'delete' as manage from book""")
table = ItemTable(items)
if request.method == 'POST':
title = request.form.get('title')
author = request.form.get('author')
publisheddate = request.form.get('publisheddate')
abook = (
""" INSERT INTO book (bookid, title, author, publisheddate)
VALUES (default, %s, %s, %s) """)
dbCon().insUpDel(abook, title, author, publisheddate)
items = dbCon().selectQName(
"""select bookid,title,author,publisheddate,
'delete' as manage from book""")
table = ItemTable(items)
return render_template('addBook.html',
form=form,
table=table,
myModaladd="myModaladd")
return render_template('addBook.html', form=form, table=table)
else:
form = LoginForm()
return render_template('login.html', form=form)
def returnBook(self):
"""The Return Book method checks if the book id entered by the user has status as borrowed.
If yes, the book is returned, otherwise it gives an error
"""
b = ("""Select bookborrowed.bookid, title
from bookborrowed inner join book
on bookborrowed.bookid = book.bookid
where lmsuserid = %s and status = 'borrowed' """)
qRtn = ("""UPDATE bookborrowed
SET status = 'returned',
returneddate = current_date
where bookid = %s and status = 'borrowed' """)
books = dbCon().selectQ(b, self.username)
if len(books) > 0:
print("| Bookid | Title |")
for r in books:
print(str(r[0]) + " | " + r[1])
allBooks = ("""SELECT * FROM bookborrowed """)
print("Enter Book ID to return")
bookID = input()
bookID = int(bookID)
if self.checkBID(bookID):
inputid = bookID
booksIdList = []
for book in books:
booksIdList.append(book[0])
# check if the book has been borrowed
bookRows = dbCon().selectQ(allBooks, self.username, inputid)
flag = True
for i in bookRows:
status = i[3][0]
if inputid not in booksIdList:
print("Invalid Book ID !")
break
elif status is 'b':
event = self.declare_event_object()
event.delete(bookID)
dbCon().insUpDel(qRtn, inputid)
print("Book return successful")
flag = False
break
if flag is True:
print("The book has not been borrowed yet")
else:
print("Invalid book ID")
else:
print("No book to return !")
def bkcheckout(self):
""" function will perform logical checks before running the borrow query
"""
event = self.declare_event_object()
# check book is already borrowed or not
q = ("""Select *
from bookborrowed
where bookid = %s """)
qBrw = (
""" INSERT INTO bookborrowed (bookborrowedid, lmsuserid, bookid,
status, borroweddate, returneddate)
VALUES (default, %s, %s, 'borrowed',
current_date, null)""")
q2 = ("""Select title
from book
where bookid = %s limit 1""")
rows = dbCon().selectQ(q, self.bkid)
flag = 0
if len(rows) is not 0:
for r in rows:
if r[2] - self.bkid is 0:
q2Chk = ("""Select *
from bookborrowed
where bookid = %s
ORDER BY bookborrowedid DESC
LIMIT 1 """)
userRows = dbCon().selectQ(q2Chk, self.bkid)
if userRows[0][3][0] is 'r':
# user can borrow book,borrow querry
title = dbCon().selectQ(q2, self.bkid)
event.insert(title[0][0], self.bkid)
dbCon().insUpDel(qBrw, self.uname, self.bkid)
print("Book borrow sucessfull")
flag = 1
else:
# user can not borrow book,reject Alert
if flag is 1:
break
else:
print("Book already borrowed")
break
else:
# borrow book since this book was never borrowed or
# returned,borrow book
title = dbCon().selectQ(q2, self.bkid)
event.insert(title[0][0], self.bkid)
dbCon().insUpDel(qBrw, self.uname, self.bkid)
print("Book borrow sucessfull")
def checkBID(self, bid):
"""check if book ID is in list of books
Arguments:
bid {int} -- book id to check
"""
qu = ("""select bookid
from book
""")
bIDs = dbCon().selectQ(qu)
bIDList = []
for ids in bIDs:
bIDList.append(ids[0])
if bid in bIDList:
return True
else:
return False
def checkBID(self, bid):
"""This functions checks if the book id entered by the user exists in the database
Arguments:
bid {string} -- Book id provided by the user
Returns:
string -- returns true if the book is found and returns false if the book is not found
"""
qu = ("""select bookid
from book
""")
bIDs = dbCon().selectQ(qu)
bIDList = []
for ids in bIDs:
bIDList.append(ids[0])
if bid in bIDList:
return True
else:
return False
def delF(id):
"""delete book
Arguments:
id {int} -- book ID to delete
Returns:
login page -- if user try to do url manipulation without login
addBook -- returns addBook page with appropriate info message
"""
if 'username' in session:
q = ("""Select *
from bookborrowed
where bookid = %s """)
rows = dbCon().selectQ(q, id)
books = dbCon().selectQ(
""" Select status
from bookborrowed inner join book
on bookborrowed.bookid = book.bookid
where bookborrowed.bookid = %s ORDER BY returneddate DESC LIMIT 1 """,
id)
if len(rows) == 0:
dbCon().insUpDel("""DELETE FROM book WHERE book.bookid = %s """,
id)
items = dbCon().selectQName(
"""select bookid,title,author,publisheddate,
'delete' as manage from book""")
table = ItemTable(items)
form = NewBook()
return render_template('addBook.html',
form=form,
table=table,
myModaldel="myModaldel")
if books[0][0] == "borrowed":
form = NewBook()
items = dbCon().selectQName(
"""select bookid,title,author,publisheddate,
'delete' as manage from book""")
table = ItemTable(items)
return render_template('addBook.html',
form=form,
table=table,
myModalcantdel="myModalcantdel")
elif books[0][0] == "returned":
dbCon().insUpDel(
"""DELETE FROM bookborrowed WHERE bookborrowed.bookid = %s """,
id)
dbCon().insUpDel("""DELETE FROM book WHERE book.bookid = %s """,
id)
form = NewBook()
items = dbCon().selectQName(
"""select bookid,title,author,publisheddate,
'delete' as manage from book""")
table = ItemTable(items)
return render_template('addBook.html',
form=form,
table=table,
myModaldel="myModaldel")
else:
form = LoginForm()
return render_template('login.html', form=form)
def servListen(self):
"""display library menu and call return borrow and search book.
"""
while True:
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
HOST = ""
PORT = 65000
ADDRESS = (HOST, PORT)
s.bind(ADDRESS)
s.listen()
print("Waiting for user login...\n")
conn, addr = s.accept()
data = conn.recv(1024)
data = data.decode()
data = json.loads(data)
conn.close()
uname = data["username"]
fname = data["fname"]
lname = data["lname"]
uEmail = data["email"]
uid = -1
row = dbCon().selectQ("""
select uname, lmsuserid, lmsuserid-lmsuserid+1
from lmsuser where uname = %s ;
""", uname)
flag = True
register = True
if len(row) > 0:
if(row[0][2] is 1):
flag = True
uid = row[0][1]
print("user found")
else:
flag = False
row = dbCon().insUpDel("""
INSERT INTO lmsuser(
lmsuserid, firstname, lastname, email, uname)
VALUES (default, %s, %s, %s, %s);
""", fname, lname, uEmail, uname)
row = dbCon().selectQ("""
SELECT lmsuserid
FROM lmsuser
where uname = %s;
""", uname)
uid = row[0][0]
flag = True
print("user registered")
print(fname + " " + lname + " has successfuly logged in.")
while(flag is True and uid > -1):
print()
print('Please select the option you want to proceed with:')
print()
print('1. Search a book')
print('2. borrow a book')
print('3. return a book')
print('4. logout')
print()
opt = input()
if(opt != '1' and opt != '2' and opt != '3' and opt != '4'):
print()
print('Please enter 1,2,3 or 4 as your choices')
elif(opt == '1'):
print("Enter book name to search")
bookTitle = input()
q = ("""select distinct
book.bookid, book.title, book.author, book.publisheddate
from book
where title like %s """)
rows = dbCon().selectQ(q, "%" + bookTitle + "%")
print("| Bookid | Title | Author | Published Date |")
for r in rows:
print(str(r[0]) + " | " + r[1] +
" | " + r[2] + " | " + str(r[3]))
elif(opt == '2'):
print("Enter Book ID to borrow")
bookID = input()
bookID = int(bookID)
if self.checkBID(bookID):
p = borrow(uid, bookID)
p.bkcheckout()
else:
print("Invalid book ID")
elif(opt == '3'):
p = Return(uid)
p.returnBook()
elif(opt == '4'):
client().clPost(addr[0])
print(fname + " " + lname + " has successfuly logged out.")
uname = ""
fname = ""
lname = ""
uEmail = ""
uid = -1
print()
break