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)