def historical():
"""
历史评分
:return: Historicalscore.html"
"""
login, userid = False, None
if 'userid' not in session:
return redirect(url_for('app.loginForm'))
else:
login, userid = True, session['userid']
historicals = []
try:
sql = '''SELECT
BookTitle,
BookAuthor,
PubilcationYear,
a.BookID,
Rating,
ImageM
FROM (SELECT * from Bookrating ) a
LEFT JOIN Books as b on a.BookID = b.BookID where a.UserID = '{}'
'''.format(userid)
historicals = mysql.fetchall_db(sql)
historicals = [[v for k, v in row.items()] for row in historicals]
except Exception as e:
logger.exception("historical rating books error: {}".format(e))
return render_template("Historicalscore.html",
books=historicals,
login=login,
useid=userid)
def order():
"""
购物车
:return: Order.html
"""
login, userid = False, None
if 'userid' not in session:
return redirect(url_for('app.loginForm'))
else:
login, userid = True, session['userid']
cats = []
try:
sql = '''SELECT b.BookID,
b.BookTitle,
b.BookAuthor,
floor((b.PubilcationYear-1000)/10) FROM (SELECT * from Cart ) a
LEFT JOIN Books as b on a.BookID = b.BookID where a.UserID = "{}"
'''.format(userid)
cats = mysql.fetchall_db(sql)
cats = [[v for k, v in row.items()] for row in cats]
except Exception as e:
logger.exception("historical rating books error: {}".format(e))
return render_template("Order.html",
books=cats,
login=login,
useid=userid)
def search():
"""
书籍检索
:return: Search.html
"""
login, userid = False, None
if 'userid' in session:
login, userid = True, session['userid']
keyword, search_books = "", []
try:
if request.method == 'GET':
keyword = request.values.get('keyword')
keyword = keyword.strip()
sql = "SELECT BookTitle, BookAuthor ,BookID, ImageM from Books where BookTitle like '%{}%' limit 20".format(
keyword)
search_books = mysql.fetchall_db(sql)
search_books = [[v for k, v in row.items()]
for row in search_books]
except Exception as e:
logger.exception("select search books error: {}".format(e))
return render_template("Search.html",
key=keyword,
books=search_books,
login=login,
useid=userid)
def root():
"""
主页
:return: home.html
"""
login, userid = False, ''
if 'userid' in session:
login, userid = True, session['userid']
# 热门书籍
hot_books = []
# sql: SELECT BookID,sum(Rating) as score FROM Book.Bookrating group by BookID order by score desc limit 10;
sql = "SELECT BookTitle, BookAuthor ,BookID, ImageM FROM Books where BookID = '" + \
"' or BookID = '".join(config['bookid']) + "'"
try:
hot_books = mysql.fetchall_db(sql)
hot_books = [[v for k, v in row.items()] for row in hot_books]
except Exception as e:
logger.exception("select hot books error: {}".format(e))
return render_template('Index.html',
login=login,
books=hot_books,
useid=userid,
name = "index")
def recommend():
"""
推荐页面
:return: Index.html
"""
login, userid, error = False, '', False
if 'userid' in session:
login, userid = True, session['userid']
# 推荐书籍
recommend_books = []
if login:
sql = """select BookTitle,
BookAuthor,
a.BookID,
a.ImageM ,
score
from (SELECT * from Books ) a
LEFT JOIN Booktuijian as b on a.BookID = b.BookID where b.UserID = "{}"
order by score desc
""".format(userid)
try:
recommend_books = mysql.fetchall_db(sql)
recommend_books = [[v for k, v in row.items()] for row in recommend_books]
except Exception as e:
logger.exception("select recommend books error: {}".format(e))
return render_template('Index.html',
login=login,
books=recommend_books,
useid=userid,
name = "recommend")
def deleteuserinfo():
'''
delete students info
:return: userinfo.html
'''
login, userid = True, None
print(request.method, 'method')
keysearch = []
try:
if request.method == 'GET':
keyUIN = request.values.get('UIN')
keyUIN = keyUIN.strip()
sql = '''DELETE FROM Students where UIN={} '''.format(keyUIN)
mysql.exe(sql)
logger.info("delete user success,sql:{}".format(sql))
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e:
logger.exception("delete user error: {}".format(e))
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def search():
"""
search the jobs
:return: Search.html
"""
login, userid = True, None
print(request.method, 'method')
formKeys = [k for k in request.args]
key_dict = {
'searchtype': None,
'searchfield': None,
'searchlocation': None,
'searchjobname': None
}
if len(formKeys) > 0:
key_dict['searchtype'] = request.args.get('searchtype')
key_dict['searchfield'] = request.args.get('searchfield')
key_dict['searchlocation'] = request.args.get('searchlocation')
key_dict['searchjobname'] = request.args.get('searchjobname')
init = False
print(key_dict)
else:
init = True
print(key_dict)
keysearch = []
try:
if request.method == 'GET':
sql = """ SELECT distinct c.companyName, j.jobName, j.Location, j.Type, j.Field, j.Title
from jobs j join Company c on c.companyID=j.companyID
where type like '%{}%' and Field like '%{}%' and Location like '%{}%'
and jobName like '%{}%' limit 20""".format(
*[n for n in key_dict.values()])
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e: # do not move
logger.exception("search jobs error: {}".format(e)) # logger
if init:
return render_template("Search.html",
login=login,
init=init,
useid=userid)
else:
return render_template("Search.html",
searchtype=key_dict['searchtype'],
searchfield=key_dict['searchfield'],
searchlocation=key_dict['searchlocation'],
searchjobname=key_dict['searchjobname'],
login=login,
init=init,
useid=userid,
keysearch=keysearch)
def adminuser():
'''
管理用户页面
'''
users = []
try:
userid = session['userid']
sql = "select * from User limit 30 "
users = mysql.fetchall_db(sql)
users = [[v for k, v in row.items()] for row in users]
return render_template('AdminUser.html',users = users, error=False, userid=userid)
except Exception as e:
logger.exception("Admin User info error: {}".format(e))
return redirect('AdminUser.html',users = users, error=True, userid="admin")
def adminbook():
'''
管理书籍页面
'''
userid = session['userid']
books = []
try:
sql = "select * from Books limit 30 "
books = mysql.fetchall_db(sql)
books = [[v for k, v in row.items()] for row in books]
except Exception as e:
logger.exception("Admin Book info error: {}".format(e))
return render_template('AdminBook.html',books = books, userid=userid)
def keyword_book():
'''
关键字查询书籍
'''
books = []
userid = session['userid']
try:
if request.method == 'POST':
keyword = request.form['keyword']
if keyword:
sql = "select * from Books where BookTitle like '%{}%' limit 30 ".format(keyword)
books = mysql.fetchall_db(sql)
books = [[v for k, v in row.items()] for row in books]
except Exception as e:
logger.exception("keyword info error: {}".format(e))
return render_template('AdminBook.html',books = books, userid=userid)
def keyword():
'''
关键字查询用户
'''
users = []
try:
userid = session['userid']
if request.method == 'POST':
keyword = request.form['keyword']
if keyword:
sql = "select UserID,Location,Age from User where userid='{}' limit 30 ".format(keyword)
users = mysql.fetchall_db(sql)
users = [[v for k, v in row.items()] for row in users]
return render_template('AdminUser.html',users = users, userid=userid)
except Exception as e:
logger.exception("keyword info error: {}".format(e))
return render_template('AdminUser.html',users = users, userid="admin")
def adminbooknextpage():
'''
管理书籍页面
'''
userid = session['userid']
books = []
try:
global nextbook
statue = nextbook*30
sql = "select * from Books limit {},30".format(statue)
nextbook+=1
books = mysql.fetchall_db(sql)
books = [[v for k, v in row.items()] for row in books]
except Exception as e:
logger.exception("Admin Book info error: {}".format(e))
return render_template('AdminBook.html',books = books, userid=userid)
def render_match():
"""
match
:return: match.html
"""
login, userid = True, None
print(request.method, 'method')
formKeys = [k for k in request.args]
if len(formKeys) > 0:
keyUIN = request.values.get('searchUIN')
keyrate = request.values.get('searchrate')
keyUIN = keyUIN.strip()
keyrate = keyrate.strip()
init = False
print(keyUIN)
print(keyrate)
else:
init = True
keysearch = []
try:
if request.method == 'GET':
sql = """call best_match ({},{})""".format(keyUIN, keyrate)
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e: # do not move
logger.exception("match jobs error: {}".format(e)) # logger
if init:
return render_template("match.html",
login=login,
init=init,
useid=userid)
else:
return render_template("match.html",
searchUIN=keyUIN,
searchrate=keyrate,
login=login,
init=init,
useid=userid,
keysearch=keysearch)
def insertuserinfo():
"""
insert a new user
:return: userinfo.html
"""
login, userid = True, None
print(request.method, 'method')
formKeys = [k for k in request.args]
key_dict = {
'UIN': None,
'studentName': None,
'Gender': None,
'Standing': None,
'Major': None,
'GPA': None
}
key_dict['UIN'] = request.args.get('UIN')
key_dict['studentName'] = request.args.get('studentName')
key_dict['Gender'] = request.args.get('Gender')
key_dict['Standing'] = request.args.get('Standing')
key_dict['Major'] = request.args.get('Major')
key_dict['GPA'] = request.args.get('GPA')
print(key_dict)
keysearch = []
try:
if request.method == 'GET':
sql = """ insert into Students
values ({}, '{}', '{}', '{}', '{}', {})""".format(
*[n for n in key_dict.values()])
mysql.exe(sql)
logger.info("insert user success,sql:{}".format(sql))
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e:
logger.exception("insert user error: {}".format(e))
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def guess():
"""
猜你喜欢
:return: Index.html
"""
login, userid, error = False, '', False
if 'userid' in session:
login, userid = True, session['userid']
# 推荐书籍
guess_books = []
if login:
sql = """select e.BookTitle,
e.BookAuthor,
e.BookID,
e.ImageM
from Books e
inner join (select c.BookID,
sum(c.Rating) as score
from (select UserID,BookID,Rating from Bookrating where Rating != 0
limit {0}) c
inner join (select UserID
from (select UserID,BookID from Bookrating where Rating != 0
limit {0}) a
inner join (select BookID from Booktuijian where UserID='{1}') b
on a.BookID=b.BookID ) d
on c.UserID=d.UserID
group by c.BookID
order by score desc
limit 10) f
on e.BookID = f.BookID""".format(config['limit'],
session['userid'])
try:
guess_books = mysql.fetchall_db(sql)
guess_books = [[v for k, v in row.items()] for row in guess_books]
except Exception as e:
logger.exception("select guess books error: {}".format(e))
return render_template('Index.html',
login=login,
books=guess_books,
useid=userid,
name="guess")
def bookinfo():
"""
书籍详情
:return: BookInfo.html
"""
# 获取用户IP
score = 0
if 'userid' not in session:
userid = None
login = False
else:
userid = session['userid']
login = True
try:
if request.method == 'GET':
bookid = request.args.get('bookid')
sql = """SELECT BookTitle,
BookID,
PubilcationYear,
BookAuthor,
ImageM from Books where BookID="{}" """.format(
bookid)
book_info = mysql.fetchall_db(sql)
book_info = [v for k, v in book_info[0].items()]
update_recommend_book(userid, bookid)
if userid:
sql = '''SELECT Rating FROM Bookrating
where UserID="{}" and BookID="{}"'''.format(
userid, bookid)
score = mysql.fetchone_db(sql)
if score:
score = int(score['Rating'])
score = math.ceil(score / 2)
if score > 10: score = 10
except Exception as e:
logger.exception("select book info error: {}".format(e))
return render_template('BookInfo.html',
book_info=book_info,
login=login,
useid=userid,
score=score)
def render_userinfo():
"""
userinfo
:return: userinfo.html
"""
login, userid = True, None
print(request.method, 'method')
keysearch = []
try:
if request.method == 'GET':
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e: # do not move
logger.exception("select students error: {}".format(e)) # logger
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def updateuserinfo():
'''
update user info
:return: userinfo.html
'''
login, userid = True, None
print(request.method, 'method')
keysearch = []
try:
if request.method == 'GET':
keyUIN = request.values.get('UIN')
keyname = request.values.get('studentName')
keygender = request.values.get('Gender')
keystanding = request.values.get('Standing')
keymajor = request.values.get('Major')
keygpa = request.values.get('GPA')
keyUIN = keyUIN.strip()
keyname = keyname.strip()
keygender = keygender.strip()
keystanding = keystanding.strip()
keymajor = keymajor.strip()
keygpa = keygpa.strip()
sql = """ update Students
set studentName='{}', Gender='{}', Standing='{}', Major='{}', GPA='{}'
where UIN = '{}' """.format(keyname, keygender, keystanding,
keymajor, keygpa, keyUIN)
mysql.exe(sql)
logger.info("update user success,sql:{}".format(sql))
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e:
logger.exception("update user error: {}".format(e))
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def render_jobinfo():
"""
jobinfo
:return: jobinfo.html
"""
login, userid = True, None
print(request.method, 'method')
keysearch = []
try:
if request.method == 'GET':
sql = """ select c.companyName, j.jobname, j.location, j.type, j.field, j.title
from jobs j join company c on c.companyID=j.companyID"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e: # do not move
logger.exception("select students error: {}".format(e)) # logger
return render_template("jobinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
