def handle_register(request):
userId = request.POST.get('userId')
password = request.POST.get('password')
nickname = request.POST.get('nickname')
email = request.POST.get('email')
startTime = int(request.POST.get('startTime')) # 前端传来的时间戳:10位int
msg = {
'userId': userId,
'password': password,
'nickname': nickname,
'email': email
}
# TO DO
# insert 之前 先查询一下有没有该账号
sql = 'INSERT INTO users(userId, password, nickname, email) VALUES(%s,%s,%s,%s)'
result_code, result = dbOptions.register_insert(sql, msg)
if result_code == 0:
# 生成token并存好
token_code, token, endTime = create_token(userId, startTime)
if token_code == 0:
data = {
'nickName': result,
'token': token,
'endTime': endTime,
'userId': userId
}
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack('生成token失败！请重试！'))
else:
return JsonResponse(package.errorPack('注册失败，您的手机号码可能已被注册，请重试！'))
def handle_recommend_tags_search(request):
"""
接口： 单个 标签搜索
:param request:
:return:
"""
wd = request.GET.get('wd')
pageno = int(request.GET.get('pageno'))
# 求对应列表
count = (pageno - 1) * 15 # 用于辅助翻页
sql_base = 'FROM br_tags LEFT JOIN br_books ON br_tags.bookId = br_books.bookId WHERE br_tags.tagName = %s ORDER BY ratingScore DESC '
sql = 'SELECT br_tags.bookId, bookName, subjectUrl, imgUrl, author, pubDate, publisher, ratingScore, ratingNum, price, ISBN, summary ' + sql_base + ' LIMIT ' + str(
count) + ',15;'
lists = [] # 返回的参数列表
result_code, lists = dbOptions.search(sql, wd)
sql_count = 'SELECT COUNT(br_tags.bookId) AS num ' + sql_base
counts = int(dbOptions.search_count(sql_count, wd)) # 查询到对应的总数
page_count = math.ceil(counts / 15) # python3:/是精确除，然后向上取整。每页15
# 给每本书 查找 tags
for i in range(len(lists)):
sql_tag = 'SELECT tagName, bookTagRank FROM br_tags WHERE bookId = %s ORDER BY bookTagRank'
result_tags_code, result_tags = dbOptions.tag_query(
sql_tag, lists[i]['bookId'])
if result_tags_code == 0:
lists[i]['tags'] = result_tags
if result_code == 0:
data = {'page_count': page_count, 'list': lists}
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack(lists[0]))
def handle_book_detail(request):
userId = request.GET.get(
"userId") # userId的可能为空===》 用户未登录 不直接从cookie中获取，避免session错误
bookId = request.GET.get("bookId")
sql = 'SELECT bookId, bookName, subjectUrl, imgUrl, author, pubDate, publisher, ratingScore, ratingNum, price, ISBN, summary FROM br_books WHERE bookId = %s'
sql_tag = 'SELECT tagName, bookTagRank FROM br_tags WHERE bookId = %s ORDER BY bookTagRank'
result_code, result = dbOptions.detail_query(sql, sql_tag, bookId)
# 返回一个loginState字段， 用于判断是否展示用户对该书本的的评分
if userId == '' or userId is None:
data = {'loginState': 0, 'loginMsg': '未登录', 'bookMsg': result}
else:
sql_favor_star = 'SELECT starNum FROM favor WHERE userId=%s AND bookId=%s'
result_favor_code, starMsg = dbOptions.star_query(
sql_favor_star, userId, bookId)
data = {
'loginState': 1,
'loginMsg': '已登录',
'bookMsg': result,
'starMsg': starMsg,
}
if result_code == 0:
return JsonResponse(package.successPack(data))
elif result_code == 1:
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack(data))
def handle_search(request):
wd = parse.unquote(request.GET.get('wd')) # url解码 涉及中文
# print('wd = ', wd)
pageno = int(request.GET.get('pageno')) # 转换成整型
# order = request.GET.get('order')
search_sql_base = 'FROM br_books WHERE MATCH(bookName, author) AGAINST(%s IN NATURAL LANGUAGE MODE)'
# 求对应列表
count = (pageno - 1) * 15 # 用于辅助翻页
sql = 'SELECT bookId, bookName, subjectUrl, imgUrl, author, pubDate, publisher, ratingScore, ratingNum, price, ISBN, summary ' + search_sql_base + ' LIMIT ' + str(
count) + ',15;'
lists = [] # 返回的参数列表
result_code, lists = dbOptions.search(sql, wd)
# 求页数page_count
sql_count = 'SELECT COUNT(bookId) ' + search_sql_base
counts = int(dbOptions.search_count(sql_count, wd)) # 查询到对应的总数
page_count = math.ceil(counts / 15) # python3:/是精确除，然后向上取整。每页15
# 给每本书 查找 tags
for i in range(len(lists)):
sql_tag = 'SELECT tagName, bookTagRank FROM br_tags WHERE bookId = %s ORDER BY bookTagRank'
result_tags_code, result_tags = dbOptions.tag_query(
sql_tag, lists[i]['bookId'])
if result_tags_code == 0:
lists[i]['tags'] = result_tags
if result_code == 0:
data = {'page_count': page_count, 'list': lists}
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack(lists[0]))
def handle_favor_star_query(request):
userId = request.POST.get('userId') # userId可能是空值'' , 可以先在前台处理
bookId = request.POST.get('bookId')
sql = 'SELECT starNum, starTime FROM favor WHERE userId=%s AND bookId=%s'
result_code, result = dbOptions.star_query(sql, userId, bookId)
if result_code == 0:
return JsonResponse(package.successPack(result))
else:
return JsonResponse(package.errorPack(result))
def handle_favor_delete(request):
userId = request.POST.get('userId')
bookId = request.POST.get('bookId')
msg = {'userId': userId, 'bookId': bookId, 'msg': '成功移除！'}
# DELETE FROM favor WHERE userId = %s AND bookId = %s
sql = 'DELETE FROM favor WHERE userId = %s AND bookId = %s'
result_code = dbOptions.favor_delete(sql, msg)
if result_code == 0:
return JsonResponse(package.successPack(msg))
else:
return JsonResponse(package.errorPack('移除失败，请重试！'))
def handle_favor_query(request):
userId = request.POST.get('userId')
# 连表查询返回
sql = 'SELECT userId, favor.bookId, starNum, bookName, subjectUrl, imgUrl, author, pubDate, publisher, ratingScore, ratingNum, price, ISBN, summary FROM favor INNER JOIN br_books ON favor.bookId = br_books.bookId WHERE userId=%s ORDER BY starTime DESC'
result_code, result = dbOptions.favor_query(sql, userId)
data = {'list': result}
if result_code == 0:
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack(result))
def handle_login(request):
"""
登录
:param request:
:return:
"""
userId = request.POST.get('userId')
pw_in = request.POST.get('password') # 前端传来的密码：密文
startTime = request.POST.get('startTime') # 前端传来的时间戳:10位int
sql = 'SELECT userId,password,nickname,email FROM users WHERE userId = %s'
result_code, result = dbOptions.login_query(sql, userId)
if result_code == 0:
pw_get = result[1] # 数据库得到的密码：明文
check_code = check_pw(pw_in, pw_get)
if check_code == 0:
# session 设置
print(result[2])
print(result[0])
# 生成token并存好
token_code, token, endTime = create_token(userId, int(startTime))
if token_code == 0:
data = {
'nickname': result[2],
'token': token,
'endTime': endTime,
}
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack('生成token失败！请重试！'))
else:
check_msg = '账号密码不匹配！'
return JsonResponse(package.errorPack(check_msg))
else:
return JsonResponse(package.errorPack(result))
def handle_home(request):
"""
用于 保持登录状态
:param request:
:return:
"""
try:
# 辨认有token是否存在、没有过期
# 若无，则找到userId对应的userName,返回
token = request.POST.get('token')
startTime = int(request.POST.get('startTime'))
sql = 'SELECT token,my_token.userId,endTime,nickname FROM my_token INNER JOIN users ON my_token.userId = users.userId WHERE token =%s'
code, msg = dbOptions.token_query(sql, token)
print('code = = = ', code)
if code == 0:
print('endTime - startTime = ')
print(msg['endTime'], '-', startTime, '=')
print(msg['endTime'] - startTime)
ge = msg['endTime'] - startTime - 86400
print('差距 = ', ge)
if ge <= 0:
data = {
'userId': msg['userId'],
'nickName': msg['nickName'],
'msg': '登录状态！',
'state_code': 0
}
return JsonResponse(package.successPack(data))
else:
tmp = {
'userId': '',
'nickName': '游客',
'msg': '身份验证已超时！请重新登录！',
'state_code': 1 # 超时
}
return JsonResponse(package.successPack(tmp))
else:
tmp = {
'userId': '',
'nickName': '游客',
'msg': '您还未登录，请重新登录！',
'state_code': 1 # 超时
}
return JsonResponse(package.successPack(tmp))
except Exception as e:
print('errrrrr', e)
return JsonResponse(package.errorPack('登录状态维持异常！'))
def handle_logout(request):
"""
'''退出'''
:param request:
:return:
"""
try:
token = request.POST.get('token')
print(token)
del_sql = 'DELETE FROM my_token WHERE token = %s'
code = dbOptions.token_delete_token(del_sql, token)
if code == 0:
data = {
'nickName': '游客',
'state': '成功退出',
'userId': '',
}
return JsonResponse(package.successPack(data))
else:
return JsonResponse(package.errorPack('token移除异常！'))
except Exception as e:
print(e)
return JsonResponse(package.errorPack('退出异常！'))
def handle_hot_query(request):
"""
热门书籍推荐
获取最近ratingNum较高的书籍
:param request:
:return:
"""
# 还可以优化： 因素：favor此书的人数
sql = 'SELECT br_books.bookId, bookName, subjectUrl, imgUrl, author,ratingNum,starTime FROM favor LEFT JOIN br_books ON favor.bookId = br_books.bookId GROUP BY br_books.bookId ORDER BY ratingNum DESC,starTime DESC LIMIT 15'
result_code, result = dbOptions.hot_query(sql)
if result_code == 0:
list = {'list': result}
return JsonResponse(package.successPack(list))
else:
return JsonResponse(package.errorPack(result))
def handle_favor_add(request):
userId = request.POST.get('userId')
bookId = request.POST.get('bookId')
starNum = request.POST.get('starNum')
starTime = request.POST.get('starTime')
print('starTime ***')
print(starTime)
print(starTime)
msg = {
'userId': userId,
'bookId': bookId,
'starNum': starNum,
'starTime': starTime,
}
sql = 'INSERT INTO favor(userId, bookId, starNum, starTime) VALUES(%s, %s, %s, %s)'
result_code = dbOptions.favor_insert(sql, msg)
if result_code == 0:
return JsonResponse(package.successPack(msg))
else:
return JsonResponse(package.errorPack('评分失败，您可能已评分或者书籍不存在！'))