def display_all_members1():
arz = setu.cursor()
arz.execute(
"SELECT member_type, member_id, full_name, mobile_number, email_id FROM members"
)
allMembers = arz.fetchall()
return allMembers
def issue_book(mem_name, book_acn):
issue = setu.cursor()
print("@issue book:")
print(book_acn)
print()
status = is_book_available(book_acn)
print(status)
if status:
mem_id = get_memid(mem_name)
print(mem_id)
if mem_id == -1:
print('member dont exists')
return -1
# wrong member id
else:
query = "insert into borrows (issue_date, issue_time, issuers_member_id, book_accession_number) values(CURDATE(), CURTIME(), %s, %s)"
query2 = "update books set is_available = 0 where accession_number=\'" + book_acn + "\';"
val = (mem_id, book_acn)
#exceptions
issue.execute(query, val)
setu.commit()
issue.execute(query2)
setu.commit()
print("Book issued")
else:
return -2
def return_book(mem_id, acn, return_date, remark):
tray = setu.cursor()
#validation of parameters
mem_status = does_member_exist(mem_id)
book_issued = is_book_issued(acn)
query = "UPDATE borrows SET return_date = %s, remarks = %s where issuers_id = %s and accession_number = %s;"
values = (return_date, remark, mem_id, acn)
query1 = "UPDATE books SET is_available = 1 where accession_number = \'" + acn + "\'"
if mem_status and book_issued:
try:
tray.execute(query, values)
tray.execute(query1)
except ms.IntegrityError as err:
print("Intergrity Error: " + str(err))
except ms.Error as err:
print("Other Error: " + str(err))
if (tray.rowcount == 1):
setu.commit()
print(str(tray.rowcount) + " Book Returned")
return True
elif (tray.rowcount == -1):
print("Error ala re")
else:
print("Jast books returned zhale wattat " + str(tray.rowcount))
else:
if mem_status == False:
return -1
elif book_issued == False:
return -2
def add_new_member(m_type, m_mno, email_id, m_fullname):
add = setu.cursor()
query = "INSERT into members(member_type,mobile_number,email_id,full_name) values(%s,%s,%s,%s)"
val = (m_type, m_mno, email_id, m_fullname)
add.execute(query, val)
setu.commit()
print(add.rowcount, " Member record inserted")
def search_book(searchby, searchfor):
arz = setu.cursor()
query = "SELECT accession_number, title, author, shelf_no, publisher, price from books where " + searchby + " like \'" + searchfor + "%\';"
print(query)
arz.execute(query)
book = arz.fetchall()
if book == []:
return -1
else:
return book
def get_memid(mem_name):
id = setu.cursor()
query = "SELECT member_id from members where full_name=\'" + mem_name + "\';"
id.execute(query)
mem_id = id.fetchall()
if mem_id == []:
return -1
else:
return mem_id[0][0]
def return_book(mem_id, book_acn, remarks=""):
wapas = setu.cursor()
query = "update borrows set return_date = CURDATE(), return_time=CURTIME(), remarks=%s where issuers_member_id=%s and book_accession_number=%s;"
val = (remarks, mem_id, book_acn)
wapas.execute(query, val)
setu.commit()
print(wapas)
print(wapas.fetchall)
print("Book returned")
def getAllUnreturnedBooks():
arz = setu.cursor()
query = "select borrows.book_accession_number, books.title, members.full_name, borrows.issue_date, members.mobile_number from((borrows inner join members on borrows.issuers_member_id = members.member_id and return_date is null) inner join books on borrows.book_accession_number = books.accession_number) order by borrows.issue_date desc;"
print(query)
arz.execute(query)
books = arz.fetchall()
print(books)
if books == []:
return -1
else:
return books
def is_book_available(book_acn):
status = setu.cursor()
print(book_acn)
query = "SELECT is_available from books where accession_number =\'" + book_acn + "\';"
print(query)
#try
status.execute(query)
state = status.fetchall()
print(state)
return state[0][0]
def list_all_books():
list = setu.cursor()
query = "SELECT * from books"
try:
list.execute(query)
except db.Error as err:
print("\n@ list_all_books -> exception")
books = list.fetchall()
return books
def is_book_available(book_acn):
status = setu.cursor()
query = "Select return_date from borrows where book_accession_number = \'"+book_acn+"\';"
status.execute(query)
temp = status.fetchall()
if temp == []:
return 0;
else:
return 1;
def add_new_member(mtype, mmno, emailid, fname):
add = setu.cursor()
query = "insert into members(member_type, mobile_number ,email_id, full_name ) values(%s,%s,%s,%s)"
val = (mtype.upper(), mmno, emailid, fname.upper())
try:
add.execute(query, val)
setu.commit()
except ms.IntegrityError as err:
return err
print(add.rowcount, " Members added into Database")
return 1
def getAllMembers():
yadi = setu.cursor()
query = "SELECT member_id, full_name, mobile_number, email_id, member_type FROM members"
try:
yadi.execute(query)
except db.Error as err:
print(err)
return -1
members = yadi.fetchall()
#print(members)
return members
def add_new_member(m_type, m_mno, email_id, m_fullname):
add = setu.cursor()
query = "INSERT into members(member_type,mobile_number,email_id,full_name) values(%s,%s,%s,%s)"
val = (m_type, m_mno, email_id, m_fullname)
try:
add.execute(query, val)
setu.commit()
except ms.IntegrityError as er:
return er
print(add.rowcount, " Member record inserted")
return 1
def add_new_book(acn, title, price, author, publisher):
add = setu.cursor()
query = "INSERT INTO books(accession_number, title, price, author, publisher) values(%s,%s,%s,%s,%s)"
val = (acn, title, price, author, publisher)
try:
add.execute(query, val)
except ms.IntegrityError as err:
err_msg = "Error: {}".format(err)
return err_msg
setu.commit()
print(str(add.rowcount) + " Book added Successfully")
return 0
def search_member_by_name1(m_name):
arz = setu.cursor()
query = "SELECT member_type, member_id, full_name, mobile_number, email_id FROM members where full_name like\'" + str(
m_name) + "%\'"
print(query)
arz.execute(query)
searchMember = arz.fetchall()
print(searchMember)
if searchMember == []:
return -1
else:
return searchMember
def search_member_by_name(m_name):
arz = setu.cursor()
query = "SELECT * FROM members where full_name like\'" + str(
m_name) + "%\'"
print(query)
arz.execute(query)
searchMember = arz.fetchall()
print(searchMember)
if searchMember == []:
return -1
else:
return searchMember
def is_book_issued(acn):
check = setu.cursor()
query = "SELECT * from borrows where accession_number = \'" + acn + "\' and return_date is NULL;"
try:
check.execute(query)
except db.Error as err:
print(str(err))
book_count = len(check.fetchall())
if book_count > 1 or book_count < 0:
return False
else:
return book_count
def does_member_exist(mid):
shodh = setu.cursor()
query = "select * from members where member_id = " + str(mid)
try:
shodh.execute(query)
except db.Error as err:
print(err)
#print(shodh.statement)
results = shodh.fetchall()
if len(results) == 1:
return True
else:
return False
def getMemberNameFromID(mem_id):
shodh = setu.cursor()
query = "SELECT full_name from members where member_id = " + str(mem_id)
try:
shodh.execute(query)
except ms.Error as err:
print(err)
return -1
mname = shodh.fetchall()
if len(mname) == 0:
return False
else:
return str(mname[0][0])
def is_book_registered(acn):
search = setu.cursor()
query = "select * from books where accession_number = \'" + str(acn) + "\'"
try:
search.execute(query)
except db.Error as err:
print(err + " \n@ is_book_registered -> exception ")
temp = search.fetchall()
if search.rowcount > 0:
return True
elif search.rowcount == 0:
return False
def getUnreturnedBooks(searchBy, searchFor):
arz = setu.cursor()
query = """select borrows.book_accession_number, books.title, members.full_name, borrows.issue_date, members.mobile_number
from((borrows inner join members on borrows.issuers_member_id = members.member_id and return_date is null)
inner join books on borrows.book_accession_number = books.accession_number)
where """ + searchBy + """ like \'""" + searchFor + """%\'
order by borrows.issue_date desc ;"""
print(query)
arz.execute(query)
books = arz.fetchall()
print(books)
if books == []:
return -1
else:
return books
def searchMember(searchBy, searchFor):
shodh = setu.cursor()
query = "SELECT member_id, full_name, mobile_number, email_id, member_type FROM members where " + searchBy + " like \'" + searchFor + "%\'"
try:
shodh.execute(query)
except db.Error as err:
print(err)
return -1
members = shodh.fetchall()
if len(members) == 0:
return False
else:
return members
def search_book(searchby, searchfor):
arz = setu.cursor()
query = "SELECT accession_number, title, shelf_no, price, authors, publisher, is_available from books where " + searchby + " like \'" + str(
searchfor) + "%\';"
try:
arz.execute(query)
except db.error as err:
print("\n@ list_all_books -> exception")
books = arz.fetchall()
if books == []:
return -1
else:
return books
def getBookName(acn):
shodh = setu.cursor()
query = "SELECT title from books where accession_number = \'" + str(
acn) + "\'"
try:
shodh.execute(query)
except db.Error as err:
print(err)
return -1
bname = shodh.fetchall()
if len(bname) == 0:
print("book not found")
return False
else:
return str(bname[0][0])
def delete_member_by_id(mid):
nsht = setu.cursor()
query = "delete from members where member_id = " + str(mid) + ";"
try:
nsht.execute(query)
setu.commit()
except db.Error as err:
print(err)
print(str(nsht.rowcount) + " member deleted Successfully")
if nsht.rowcount == 0:
return -1
elif nsht.rowcount == 1:
return 1
else:
return nsht.rowcount
def insert_new_book(acn, title, shelf_no, price, author, publisher):
add = setu.cursor()
query = "INSERT INTO books(accession_number, title, shelf_no, price, authors, publisher, is_available) values(%s,%s,%s,%s,%s,%s,%s)"
val = (acn, title, shelf_no, price, author, publisher, '1')
try:
add.execute(query, val)
except db.Error as err:
print(format(err))
return err
except db.IntegrityError as err:
err = "Error: {}".format(err)
return err
setu.commit()
print(str(add.rowcount) + " Book added Successfully")
return True
def delete_book(acn):
delete = setu.cursor()
query = "delete from books where accession_number = \'" + str(acn) + "\'"
try:
delete.execute(query)
except db.Error as err:
print(err)
return err
setu.commit()
print(str(delete.rowcount) + " Books Deleted Successfully")
if delete.rowcount == 0:
return -1
elif delete.rowcount == 1:
return 1
else:
return delete.rowcount
def issue_book(mem_id, acn, issue_date):
tray = setu.cursor()
#validation of parameters
mem_status = does_member_exist(mem_id) #
book_status = is_book_available(
acn
) # if returns -1 pop error " wrong accession no." (book exists check is implicit)
if mem_status and book_status:
#print(acn + " " + mem_id)
#print(issue_date)
query = "INSERT into borrows(issue_date, issuers_id, accession_number) values(%s, %s, %s)"
query1 = "UPDATE books set is_available = 0 where accession_number=\'" + str(
acn) + "\'"
values = (issue_date, mem_id, acn)
try:
tray.execute(query, values)
tray.execute(query1)
except ms.IntegrityError as err:
print("Intergrity Error: " + str(err))
except ms.Error as err:
print("Other Error: " + str(err))
print(str(tray.statement))
if (tray.rowcount == 1):
setu.commit()
print(str(tray.rowcount) + " Book issues")
return True
elif (tray.rowcount == -1):
print("Error ala re")
return False
else:
print("Jast books issue zhale wattat " + str(tray.rowcount))
return False
else:
if mem_status == False:
return -1
elif book_status == False:
return -2
def getUnreturnedBooks(searchBy, searchFor):
pustak = setu.cursor()
query = """select borrows.accession_number, books.title, members.full_name, borrows.issue_date, members.mobile_number, members.member_id
from((borrows inner join members on borrows.issuers_id = members.member_id and return_date is NULL)
inner join books on borrows.accession_number = books.accession_number )
where """ + searchBy + """ like \'""" + searchFor + """%\' order by borrows.issue_date desc ;"""
try:
pustak.execute(query)
except ms.Error as err:
print(err)
return -1
books = pustak.fetchall()
if len(books) == 0:
return False
else:
return books
