def is_isbn_available_for_checkout (isbn):
with sql_connector() as sql:
sql.run(
'''
select exists(
select *
from book_loans
where isbn = '{0}' and date_in is null
)
'''.format(isbn)
)
print(
'''
select exists(
select *
from book_loans
where isbn = '{0}' and date_in is null;
)
'''.format(isbn)
)
loan_results = sql.getall()
available = 'Yes'
if loan_results[0][0] == 1:
available = 'No'
return available
def check_out_book_to_user(book_id, user_id):
with sql_connector() as sql:
sql.run(
'''
insert into book_loans(isbn, card_id, date_out, due_date) values('{0}', '{1}', '{2}', '{3}')
'''.format(book_id, int(user_id[2:]), dt.date.today().strftime('%Y-%m-%d'), (dt.date.today() + dt.timedelta(days=14)).strftime('%Y-%m-%d'))
)
def check_in_book(loan_id):
with sql_connector() as sql:
sql.run(
'''
update book_loans
set date_in = '{0}'
where loan_id = {1}
'''.format(dt.date.today().strftime('%Y-%m-%d'), loan_id)
)
def books_unreturned_by_user(user_id):
with sql_connector() as sql:
sql.run(
'''
select count(*) from book_loans where card_id = {0} and date_in is null;
'''.format(int(user_id[2:]))
)
result = sql.getall()
return 0 if result == None else result[0][0]
def find_records_for_checkin(search_text, id_num):
with sql_connector() as sql:
sql.run(
'''
select loan_id, isbn, card_id, name
from book_loans natural join borrowers
where (name like '%{0}%' or isbn like '%{0}%' or {1} = card_id) and date_in is null;
'''.format(search_text, id_num)
)
return sql.getall()
def name_from_userid(user_id):
with sql_connector() as sql:
sql.run(
'''
select ssn, name from borrowers where card_id = {0};
'''.format(int(user_id[2:]))
)
result = sql.getone()
if result == None: return result
return result[1]
def title_from_isbn (isbn):
with sql_connector() as sql:
sql.run(
'''
select title from books where isbn = '{0}';
'''.format(isbn)
)
result = sql.getone()
if result == None: return result
return result[0]
def borrower_info(ssn):
with sql_connector() as sql:
sql.run('''
select *
from borrowers
where ssn = '{}'
'''.format(ssn)
)
results = sql.getall()
return (['unknown'] * 5) if results == None else results[0]
def add_new_borrower(ssn, name, addr, phone):
with sql_connector() as sql:
try:
sql.run(
'''
insert into borrowers(ssn, name, address, phone)
values('{}', '{}', '{}', '{}');
'''.format(ssn, name, addr, phone), False
)
return 'Success'
except Exception as e:
return 'UserExists' if 'Duplicate' in str(e) else 'UnknownError'
def books_matching_terms(search_terms):
# Synthesize the SQL query
search_query = search_books1
for search_term in search_terms:
search_query += search_books2.format(search_term)
search_query += search_books3
for search_term in search_terms:
search_query += search_books4.format(search_term)
search_query += search_books5
#Run the SQL query for the search terms
with sql_connector() as sql:
sql.run(search_query)
results = sql.getall()
return results;
def fines(request):
response = basic_response()
# if a fine was paid for a loan_id
if 'paid' in request.GET:
loan_id = request.GET['paid']
with sql_connector() as sql:
sql.run('''
update fines
set paid = true
where loan_id = {}
'''.format(loan_id))
# if date was submitted then, update the fines
if 'date' in request.GET:
date = request.GET['date']
with sql_connector() as sql:
# Add fines not already present, whose due dates are exceeded as of today: Case 1: Book not yet returned
sql.run('''
insert into fines
select loan_id, 0.25 * datediff('{0}', due_date), false
from book_loans
where ('{0}' > due_date) and (not loan_id = any (select loan_id from fines)) and (date_in is null)
'''.format(date))
# Add fines not already present, whose due dates are exceeded as of today: Case2 : Book returned
sql.run('''
insert into fines
select loan_id, 0.25 * datediff(date_in, due_date), false
from book_loans
where ('{0}' > due_date) and (date_in > due_date) and (not loan_id = any (select loan_id from fines)) and (date_in is not null)
'''.format(date))
# Fines updated only for those which not yet returned. second category fines updated only
sql.run('''
update fines
set fine_amt = 0.25 * datediff('{0}', (select due_date from book_loans where book_loans.loan_id = fines.loan_id))
where paid = false and loan_id = any (select loan_id from book_loans where date_in is null)
'''.format(date))
# This date occured box
response.write('''
<h3><b>Make this date happen (as a daily script would run on increasing dates, choose only increasing dates in succession):</b></h3>
<form method='get'>
<input type='date' name='date'>
<br>
<input type='submit' value='This date occured'>
</form>
''')
# Pay fines for a user table
response.write('''
<form>
<input type='submit' value='Pay fines'>
<table class='t1' width='100%' >
<tr>
<th>Select</th>
<th>UserID</th>
<th>User name</th>
<th>Fine amount</th>
</tr>
''')
with sql_connector() as sql:
sql.run('''
select book_loans.card_id, borrowers.name, sum(fine_amt)
from (book_loans natural join borrowers) natural join fines
where fines.paid = false
group by book_loans.card_id, borrowers.name
having sum(fine_amt) > 0
''')
results = sql.getall()
for i, result in enumerate(results):
card_id = 'ID' + str(10000000 + int(result[0]))[2:]
response.write('''
<tr>
<td><input type='radio' name='paying_user' value='{0}'>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
</tr>
'''.format(card_id, result[1], result[2]))
response.write('''
</table>
</form>
''')
if 'paying_user' in request.GET:
paying_user_id = int(request.GET['paying_user'][2:])
with sql_connector() as sql:
sql.run('''
select book_loans.loan_id, book_loans.isbn, books.title, book_loans.date_out, book_loans.due_date, book_loans.date_in, fines.fine_amt
from (book_loans natural join books) inner join fines on book_loans.loan_id = fines.loan_id
where fines.paid = false and book_loans.card_id = {0};
'''.format(paying_user_id))
results = sql.getall()
response.write('''
<form>
<input type='submit' value='Fine paid'>
<table width='100%' class='t1'>
<tr>
<th>Select</th>
<th>Loan ID</th>
<th>ISBN</th>
<th>Book title</th>
<th>Out date</th>
<th>Due date</th>
<th>Date returned</th>
<th>Fine amount</th>
</tr>
''')
for i, result in enumerate(results):
if result[5] == None:
response.write('''
<tr>
<td><input type='radio' disabled></td>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
<td>{3}</td>
<td>{4}</td>
<td><a href='/app/checkin/?search={1}'>Return</a></td>
<td>{5}</td>
</tr>
'''.format(result[0], result[1], result[2], result[3],
result[4], 'Unfinalized'))
else:
response.write('''
<tr>
<td><input type='radio' name='paid' value='{0}'></td>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
<td>{3}</td>
<td>{4}</td>
<td>{5}</td>
<td>{6}</td>
</tr>
'''.format(result[0], result[1], result[2], result[3],
result[4], result[5], result[6]))
response.write('''
</table>
</form>
''')
return response
import pandas as pd
from sql_connector import sql_connector
import install_config as ic
import datetime as dt
#TODO: Delete in order of foreign key
if ic.install_data1 == True:
# Read the book data into a pandas dataframe
data_file_path = 'data/books.csv'
book_data = pd.read_table(data_file_path, encoding='latin-1')
# Drop the existing tables for a clean install
with sql_connector() as sql:
# in order of constraints
sql.run('drop table fines')
sql.run('drop table book_loans')
sql.run('drop table authors')
sql.run('drop table books_raw')
sql.run('drop table books')
# Create the authors table
with sql_connector() as sql:
# Create the authors table
sql.run('''
create table authors(
author_id int not null auto_increment,
name varchar(50) not null,
primary key (author_id),
unique (name));
''')
