def my_reviews():
"""
This allows the visitor to see all the reviews that he has posted.
"""
id = session["user_id"]
page = request.args.get('page', 1, type=int)
total = db.execute("SELECT COUNT (*) FROM reviews WHERE user_id = :id", {
'id': id
}).fetchone()
res = db.execute(
"SELECT users.username, books.id, books.title, reviews.content, \
reviews.date_posted, reviews.rating FROM users JOIN reviews ON users.id = reviews.user_id\
JOIN books ON books.id = reviews.books_id WHERE user_id = :id ORDER BY date_posted DESC OFFSET ((:page -1) * 5)\
ROWS FETCH NEXT 5 ROWS ONLY", {
'id': id,
'page': page
}).fetchall()
total_pages = math.ceil(total[0] / 5)
page_list = pagination(page, total_pages)
return render_template('my_reviews.html',
res=res,
total=total[0],
page=page,
id=id,
page_list=page_list)
def search():
"""
search books by title, author or isbn using partial or complete names and numbers then display
10 results per page.
"""
q = request.args.get("query")
s = "%" + q + "%"
# default to first page
page = request.args.get('page', 1, type=int)
# get total result count
total = db.execute(
"SELECT COUNT (*) FROM books WHERE title LIKE :s OR author LIKE :s OR\
isbn LIKE :s", {
's': s
}).fetchone()
res = db.execute(
"SELECT * FROM books WHERE title LIKE :s \
OR author LIKE :s OR isbn LIKE :s ORDER BY id ASC OFFSET ((:page -1) * 10)\
ROWS FETCH NEXT 10 ROWS ONLY;", {
's': s,
'page': page
}).fetchall()
# calculate number of pages
total_pages = math.ceil(total[0] / 10)
page_list = pagination(page, total_pages)
return render_template('search.html',
res=res,
q=q,
total=total[0],
page_list=page_list,
page=page)
def easyroommate(page):
plist = []
total_count = Property.query.filter_by(spider_id=3).count()
pagination_params = pagination(page, total_count, COUNT_PER_PAGE)
results = Property.query.filter_by(spider_id=3).order_by(
Property.id.desc()).paginate(page, COUNT_PER_PAGE, False).items
if results:
for result in results:
plist.append({
'id': result.id,
'spider_name': result.spider.name,
'location': result.location,
'price': result.price,
'image_url': result.image_url,
'about_the_flatshare': result.about_the_flatshare,
'who_lives_there': result.who_lives_there,
'ideal_flatmates': result.ideal_flatmates,
'description': result.description,
})
return render_template('easyroommate.html',
data=plist,
pagination=pagination_params,
scrapyd_url=scrapyd_url)
def midlandici(page):
plist = []
total_count = Property.query.filter_by(spider_id=2).count()
pagination_params = pagination(page, total_count, COUNT_PER_PAGE)
results = Property.query.filter_by(spider_id=2).order_by(
Property.id.desc()).paginate(page, COUNT_PER_PAGE, False).items
if results:
for result in results:
plist.append({
'id': result.id,
'spider_name': result.spider.name,
'location': result.location,
'date': result.date,
'buildling': result.buildling,
'size': result.size,
'ft_price': result.ft_price,
'op_type': result.op_type,
'price': result.price,
'data_source': result.data_source,
})
return render_template('midlandici.html',
data=plist,
pagination=pagination_params,
scrapyd_url=scrapyd_url)
def centadata(page):
plist = []
total_count = Property.query.filter_by(spider_id=1).count()
pagination_params = pagination(page, total_count, COUNT_PER_PAGE)
results = Property.query.filter_by(spider_id=1).order_by(
Property.id.desc()).paginate(page, COUNT_PER_PAGE, False).items
if results:
for result in results:
plist.append({
'id': result.id,
'spider_name': result.spider.name,
'location': result.location,
'unit_floor': result.unit_floor,
'building_age': result.building_age,
'number_of_units': result.number_of_units,
'building_type': result.building_type,
'gross_price': result.gross_price,
'gross_area': result.gross_area,
'gross_per_foot': result.gross_per_foot,
'net_price': result.net_price,
'net_area': result.net_area,
'net_per_foot': result.net_per_foot,
})
return render_template('centadata.html',
data=plist,
pagination=pagination_params,
scrapyd_url=scrapyd_url)
def activity():
if 'account' in session:
if session['account']['privilege'] == 'admin':
args = request.args.to_dict()
page = int(args['page']) if 'page' in args else 1
max_entries = 3
max_page, offset = pagination(db, page,
max_entries, table='transaction')
transactions = fetch_transactions(db,
max_entries, offset)
return render_template('activity.html',
account=session['account'],
transactions=transactions,
page=page,
pages=range(1, max_page))
else:
return redirect(url_for('store'))
else:
return redirect(url_for('login'))
def users():
if 'account' in session:
if session['account']['privilege'] == 'admin':
args = request.args.to_dict()
username = \
str(args['username']) if 'username' in args else None
page = int(args['page']) if 'page' in args else 1
max_entries = 10
max_page, offset = pagination(db, page,
max_entries, table='account')
usernames = fetch_usernames(db, max_entries, offset)
account = fetch_account(db, username, verbose=True)
return render_template('users.html',
account=session['account'],
usernames=usernames,
page=page,
pages=range(1, max_page),
account_info=account)
else:
return redirect(url_for('store'))
else:
return redirect(url_for('login'))
def book(id):
"""
Display searched book and list reviews and ratings.
"""
page = request.args.get('page', 1, type=int)
total = db.execute("SELECT COUNT (*) FROM reviews WHERE books_id = :id", {
'id': id
}).fetchone()
res = db.execute(
"SELECT id, title, author, year, isbn FROM books WHERE id =:id", {
'id': id
}).fetchone()
reviews = db.execute(
"SELECT users.username, books.title, reviews.content, reviews.date_posted \
FROM users JOIN reviews ON users.id = reviews.user_id JOIN books ON books.id = reviews.books_id \
WHERE books_id = :id ORDER BY date_posted DESC OFFSET ((:page -1) * 5)\
ROWS FETCH NEXT 5 ROWS ONLY", {
'id': id,
'page': page
}).fetchall()
# get average rating and total reviews from goodreads
average_rating = good_reads(res.isbn)['average_rating']
work_ratings_count = good_reads(res.isbn)['work_ratings_count']
# get total pages
total_pages = math.ceil(total[0] / 5)
page_list = pagination(page, total_pages)
return render_template('book.html',
res=res,
total=total[0],
page=page,
reviews=reviews,
id=id,
page_list=page_list,
average_rating=average_rating,
work_ratings_count=work_ratings_count)