def rank():
cur.execute("""
SELECT r.id_r, r.name
FROM librarians.borrows AS b, librarians.readers AS r
WHERE b.give_back='1' AND r.id_r=b.name_id;
""")
ranks = {}
for i in cur.fetchall():
if int(i[0]) in ranks:
ranks[int(i[0])]['count'] += 1
else:
ranks[int(i[0])] = {}
ranks[int(i[0])]['count'] = 1
ranks[int(i[0])]['name'] = i[1].split(' ')[0]
ranks = sorted(ranks.items(), key=lambda item: item[1]['count'], reverse=True)
rank = []
for r in range(10):
try:
rank.append(ranks[r])
except IndexError:
break
return render_template('rank.html', ranks=rank)
def books():
cur.execute(
"""
SELECT bo.title, bor.rented, bor.return, bo.id_b, bor.give_back
FROM librarians.borrows AS bor, librarians.books AS bo
WHERE bor.name_id=%s AND bor.book_id=bo.id_b
ORDER BY bor.id_br DESC;
""", (session['auth']['id'], ))
return render_template('mybook.html', bor=cur.fetchall())
def remind():
if request.method == 'POST':
if 'email' in request.form:
cur.execute("SELECT id_r FROM librarians.readers WHERE email=%s;",
(request.form['email'], ))
resp = cur.fetchone()
if resp:
code = ''.join(
sample('qwertyuiopasdfghjklzxcvbnm1234567890', 32))
pass_code.append((code, resp[0]))
text = open("email/RemindPass.txt").read()
smtp.sendEmail(request.form['email'],
'Sort Books - Przypomnienie hasła',
text.format(url=request.url + '?key=' + code))
return render_template('remind.html', step=2)
else:
return render_template(
'remind.html',
step=1,
error="Żadne konto nie jest przypisane do tego adresu email"
)
elif 'key' in request.form:
if request.form['pass'] == request.form['pass2']:
index = [x for x in pass_code
if x[0] == request.form['key']][0]
pass_code.remove(index)
cur.execute(
"UPDATE librarians.readers SET pass=%s WHERE id_r=%s;",
(sha512(request.form['pass'].encode('UTF-8')).hexdigest(),
index[1]))
return render_template('remind.html', step=4)
else:
return render_template('remind.html',
step=3,
key=request.form['key'],
error="Hasła nie są identyczne")
else:
return render_template('remind.html',
step=1,
error="Wystąpił błąd w zapytaniu")
else:
if request.args.get("key"):
try:
if request.args.get("key") == [x[0] for x in pass_code][0]:
return render_template('remind.html',
step=3,
key=request.args.get("key"))
except IndexError:
pass
return render_template('remind.html',
step=1,
error="Wystąpił błąd w zapytaniu")
else:
return render_template('remind.html', step=1)
def book_info(id_book):
allow_com = False
if 'auth' in session:
cur.execute(
"SELECT id_br FROM librarians.borrows WHERE book_id=%s AND name_id=%s;",
(id_book, session['auth']['id']))
if cur.fetchone():
allow_com = True
if request.method == 'GET':
cur.execute(
"SELECT * FROM librarians.comments WHERE id_b =%s AND accept='1';",
(id_book, ))
comments = cur.fetchall()
cur.execute("SELECT * FROM librarians.books WHERE id_b=%s;",
(id_book, ))
resp = cur.fetchone()
get_image = get_api() + "/api/get_image?name=" + secure_filename(
resp[1])
image = get(get_image)
print(image.status_code)
if image.status_code == 200:
image = get_image
if resp[3] == 1:
item = 'Dostępny jeden egzemplarz'
elif resp[3] > 0:
item = 'Dostępnych ' + str(resp[3]) + ' egzemplarzy'
else:
cur.execute(
"SELECT return FROM librarians.borrows WHERE book_id=%s AND give_back='false' ORDER BY return;",
(id_book, ))
ret = cur.fetchall()
item = 'Książka będzie dostępna najwcześniej w ' + ret[0][
0].isoformat()
return render_template('book.html',
book=resp,
item=item,
allow_com=allow_com,
comments=comments,
image=image)
else:
if not 'auth' in session or not allow_com:
return redirect(
url_for('book_blueprint.book_info', id_book=id_book))
cur.execute(
"INSERT INTO librarians.comments VALUES (default , %s, %s, %s, %s, '0');",
(id_book, request.form['comment'],
datetime.datetime.now().strftime('%d-%m-%y'),
session['auth']['username']))
return redirect(url_for('book_blueprint.book_info', id_book=id_book))
def settings():
cur.execute("SELECT * FROM librarians.readers_pref WHERE reader_id = %s;",
(session['auth']['id'], ))
pref = cur.fetchone()
if request.method == 'POST':
if 'pass' in request.form:
cur.execute("SELECT pass FROM librarians.readers WHERE id_r=%s;",
(session['auth']['id'], ))
if sha512(request.form['old_pass'].encode('UTF-8')).hexdigest() == cur.fetchone()[0]\
and request.form['new_pass'] == request.form['new_pass2']:
cur.execute(
"UPDATE librarians.readers SET pass=%s WHERE id_r = %s;",
(sha512(
request.form['new_pass'].encode('UTF-8')).hexdigest(),
str(session['auth']['id'])))
return render_template('settings.html', pref=pref, update=True)
else:
return render_template('settings.html',
pref=pref,
update='Hasła się nie zgadzają')
elif 'email' in request.form:
cur.execute(
"UPDATE librarians.readers SET email=%s WHERE id_r = %s;",
(request.form['email'], session['auth']['id']))
return render_template('settings.html', pref=pref, update=True)
elif 'privacy' in request.form:
if request.form.getlist('allow_email'): allow_email = 'true'
else: allow_email = 'false'
if request.form.getlist('allow_address'): allow_address = 'true'
else: allow_address = 'false'
if request.form.getlist('allow_profile'): allow_profile = 'true'
else: allow_profile = 'false'
cur.execute(
"UPDATE librarians.readers_pref"
"SET allow_email=%s, allow_address=%s, allow_profile=%s"
"WHERE reader_id = %s;",
(allow_email, allow_address, allow_profile,
session['auth']['id']))
return render_template('settings.html', pref=pref, update=True)
else:
return render_template('settings.html', pref=pref)
def profile(user_id):
cur.execute(
"SELECT * FROM librarians.readers WHERE id_r=%s AND loged='true';",
(user_id, ))
resp = cur.fetchone()
if not resp:
cur.execute(
"SELECT * FROM librarians.readers_pref WHERE reader_id = %s;",
(user_id, ))
pref = cur.fetchone()
if not pref[3] and not 'auth' in session:
return abort(404)
else:
cur.execute(
"""
SELECT bo.title, bo.id_b
FROM librarians.books AS bo, librarians.borrows AS br
WHERE br.name_id=%s AND br.book_id=bo.id_b AND br.give_back='true';
""", (user_id, ))
count = cur.fetchall()
info = {
'name': resp[1],
'book_count': len(count),
'book_title': '',
'email': 'ukryto',
'address': 'ukryto',
'owner': False
}
books = []
for book in count:
if not any(book[0] in s for s in books):
books.append((book[1], book[0]))
if pref[1] or 'auth' in session and user_id == session['auth'][
'id']:
info['email'] = resp[3]
if pref[2] or 'auth' in session and user_id == session['auth'][
'id']:
info['address'] = resp[2]
return render_template('profile.html', user=info, books=books)
else:
return abort(404)
def avalibe():
cur.execute("SELECT * FROM librarians.books WHERE id_b>0;")
resp = cur.fetchall()
return render_template('books.html', books=resp)
def auth_adjust():
if session['auth']['adj']:
if request.method == 'POST':
if 'email' in request.form:
if request.form['email'] == request.form['email2']:
cur.execute(
"SELECT * FROM librarians.readers WHERE email=%s;",
(request.form['email'], ))
if not cur.fetchone():
cur.execute(
"UPDATE librarians.readers SET email=%s WHERE id_r=%s;",
(request.form['email'], session['auth']['id']))
cache = session['auth']
cache['email'] = request.form['email']
session['auth'] = cache
return render_template('adj.html', step=2)
else:
return render_template(
'adj.html',
error="Podany adres email jest już w użyciu",
step=1)
else:
return render_template(
'adj.html',
step=1,
error="Podane adresy email nie są identyczne")
elif 'pass' in request.form:
if request.form['pass'] == request.form['pass2']:
cur.execute(
"UPDATE librarians.readers SET pass=%s WHERE id_r=%s;",
(sha512(
request.form['pass'].encode('UTF-8')).hexdigest(),
session['auth']['id']))
return render_template('adj.html', step=3)
else:
return render_template(
'adj.html',
step=2,
error="Podane hasła nie są identyczne")
else:
if request.form.getlist('allow_email'): allow_email = 'true'
else: allow_email = 'false'
if request.form.getlist('allow_address'):
allow_address = 'true'
else:
allow_address = 'false'
if request.form.getlist('allow_profile'):
allow_profile = 'true'
else:
allow_profile = 'false'
cur.execute(
"INSERT INTO librarians.readers_pref VALUES (%s, %s, %s, %s);",
(session['auth']['id'], allow_email, allow_address,
allow_profile))
cur.execute(
"UPDATE librarians.readers SET loged=true WHERE id_r=%s;",
(session['auth']['id'], ))
text = open("email/NewUserEmail.txt").read()
smtp.sendEmail(session['auth']['email'],
'Sort Books - Witamy!',
text.format(name=session['auth']['username']))
return render_template('adj.html', step=4)
else:
return render_template('adj.html', step=1)
else:
return redirect(url_for('index_blueprint.index'))
def auth(login, pas):
cur.execute(
"SELECT * FROM librarians.readers WHERE (login=%s OR email=%s) AND pass=%s;",
(login, login, pas))
return cur.fetchone()
