def reset_user_password(user_name, email):
"""
用户重置密码
:param user_name: 用户名
:param email: 邮箱
:return:
"""
recipient = email
subject = "reset password"
# 重新生成8位长度的密码
from tool.util import random_str
new_password = random_str()
content = "您的新密码: " + new_password + "\n请在24小时内修改默认密码!"
print recipient, subject, content
if recipient and subject and content:
from lib.email import mailnotify
mbres = mailnotify.send_notifymail(recipient, subject, content)
if not mbres:
return False, "发送失败"
else:
return False, "请求参数不正确"
# 邮件发送成功
# 更新用户密码, 添加用户重置记录
db_manager = DBManager()
password = hashlib.sha224(new_password).hexdigest()
sql0 = "update `%s` set password='******' where username='******'" % ("tb_account", password, user_name)
cursor0 = db_manager.conn_r.cursor()
try:
cursor0.execute(sql0)
db_manager.conn_r.commit()
cursor0.close()
except Exception:
db_manager.conn_r.rollback()
cursor0.close()
db_manager.close()
raise Exception
return False, "密码更新失败"
sql1 = "insert into `%s` (username, reset_time) values ('%s', '%s')" % ("tb_reset", user_name, time_now_str())
cursor1 = db_manager.conn_r.cursor()
try:
cursor1.execute(sql1)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
return False, "添加重置记录失败"
db_manager.close()
is_success = True
result = dict()
result['new_password'] = new_password
return is_success, result
def login(username, password, identifier):
"""
用户登录 (学生, 教师登录入口)
:param username: 用户名
:param password: 密码
:return:
"""
password = hashlib.sha224(password).hexdigest()
db_manager = DBManager()
sql = "select *from `tb_account` as ta inner join `tb_user` as tu on ta.username=tu.username and ta.username='******'" \
" and ta.password='******' and tu.identifier=%s" % (username, password, identifier)
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
is_ok = False
data = None
if result:
# 是否为新用户登录
is_new_user = False
sql = "select *from `%s` where username='******'" % ("tb_user_log", username)
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
is_new_user = True
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
is_ok = True
data = result0
# 拿到签到数据
sql1 = "select *from `%s` where username='******' and to_days(sign_time)=to_days(now())" % ("tb_sign", username)
cursor1 = db_manager.conn_r.cursor()
cursor1.execute(sql1)
result1 = cursor1.fetchone()
cursor1.close()
has_sign_today = 0
if result1 is not None:
has_sign_today = 1
data['has_sign_today'] = has_sign_today
# 如果是新用户登录, 更新积分
if is_new_user:
user_operation_type = 1
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template",
user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points']
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
# 记录用户登录日志
sql_0 = "insert into `%s` (username, login_time) values ('%s', '%s') ON DUPLICATE KEY UPDATE" \
" login_time=login_time" % ("tb_user_log", username, time_now_str())
cursor1 = db_manager.conn_r.cursor()
try:
cursor1.execute(sql_0)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
db_manager.close()
return is_ok, data
def ask_question(username, answer_id, ask_content, original_question_id, be_asked_username, options=None):
"""
用户追问
:param username: 用户名
:param answer_id: 回答的ID
:param ask_content: 追问内容
:param original_question_id: 原问题ID
:param be_asked_username: 被追问的用户
:return:
"""
db_manager = DBManager()
# 问题是否已经被解决
sql_0 = "select question_status from `%s` where question_id=%s" % ("tb_question", original_question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
question_status = result_0['question_status']
# 问题已经被解决
if question_status == 1:
return False, "问题已经被解决"
# 检验被提问的用户是否回答过该问题
sql_0 = "select *from `tb_answer` where answer_username='******' and question_id=%s" % (be_asked_username,
original_question_id)
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None or len(result_0) == 0:
return False, "没有回答该问题"
# 拿到追问顺序ask_order
sql_1 = "select max(ask_order) as ask_order0 from `tb_ask` where be_asked_username='******' and original_question_id=" \
"%s" % (be_asked_username, original_question_id)
cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_1.execute(sql_1)
result_1 = cursor_1.fetchone()
cursor_1.close()
if result_1 is None or len(result_1) == 0:
ask_order = 0
else:
ask_order = result_1['ask_order0'] if result_1['ask_order0'] else 0
# 添加一条追问记录
props = dict()
props['ask_content'] = ask_content
props['ask_time'] = time_now_str()
props['original_question_id'] = original_question_id
props['be_asked_username'] = be_asked_username
props['ask_order'] = ask_order + 1
props['answer_id'] = answer_id
if options:
assert isinstance(options, dict)
props.update(options)
insert_sql = forEachPlusInsertProps("tb_ask", props)
msg0 = "[in ask_question] insert_sql=" + insert_sql
logging.info(msg0)
cursor_2 = db_manager.conn_r.cursor()
try:
cursor_2.execute(insert_sql)
db_manager.conn_r.commit()
cursor_2.close()
except Exception:
db_manager.conn_r.rollback()
cursor_2.close()
db_manager.close()
raise Exception
db_manager.close()
return True, ""
def answer_question(username, question_id, answer_content, is_original_answer, options=None):
"""
回答问题
:param username: 用户名
:param question_id: 问题ID
:param answer_content: 回答内容
:param is_original_answer: 是否是原回答(1: 原回答 0: 追答)
:return:
"""
db_manager = DBManager()
# 判断用户是否有权限回答问题
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False, "用户不存在"
identifier = result0['identifier']
if identifier != 1:
return False, "没有回答权限"
original_question_id = question_id
# 如果是追答
if is_original_answer == 0:
# 拿到问题原始ID
sql_0 = "select original_question_id from `%s` where ask_question_id=%s" % ("tb_ask", question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
original_question_id = result_0['original_question_id']
# 问题是否已经被解决
sql_0 = "select question_status from `%s` where question_id=%s" % ("tb_question", original_question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
question_status = result_0['question_status']
# 问题已经被解决
if question_status == 1:
return False, "问题已经被解决"
props = dict()
props['answer_username'] = username
props['answer_time'] = time_now_str()
props['question_id'] = question_id
props['is_accepted'] = 0
props['answer_content'] = answer_content
if options:
assert isinstance(options, dict)
props.update(options)
sql = forEachPlusInsertProps("tb_answer", props)
msg = "[in answer_question] sql=" + sql
logging.info(msg)
cursor = db_manager.conn_r.cursor()
try:
cursor.execute(sql)
db_manager.conn_r.commit()
cursor.close()
except Exception:
db_manager.conn_r.rollback()
cursor.close()
db_manager.close()
raise Exception
# 更新用户积分 (只有原始问题的回答加分, 追答不加分)
if is_original_answer == 1:
user_operation_type = 4
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points']
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
msg_110 = "[in answer_question refresh user points] sql_0=" + sql_0
logging.info(msg_110)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True, "回答成功"
def connect_question(username, question_id):
"""
收藏问题
:param username: 用户名
:param question_id: 问题ID
:return:
"""
db_manager = DBManager()
# 问题是否存在
sql0 = "select *from `%s` where question_id=%s" % ("tb_question", question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False, "问题不存在"
# 查询是否收藏过该问题
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
username, question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is not None:
return False, "已收藏过该问题"
sql = "insert into `%s` (question_id, collecter_username, collect_time) values (%s, '%s', '%s')" % ("tb_question_collection",
question_id, username, time_now_str())
msg = "[in connect_question] sql=" + sql
logging.info(msg)
cursor = db_manager.conn_r.cursor()
try:
cursor.execute(sql)
db_manager.conn_r.commit()
except Exception:
db_manager.conn_r.rollback()
raise Exception
db_manager.close()
return True, ""
def post_question(username, grade, subject, question_content, question_score=0, options=None):
"""
用户提问
:param username: 用户名
:param grade: 年级
:param subject: 学科
:param question_content: 问题内容
:param question_score: 悬赏积分
:param options: 可变字段
:return:
"""
db_manager = DBManager()
# 判断用户是否有权限提问
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False
identifier = result0['identifier']
if identifier != 0:
return False
# 客户端提交的问题内容为空
if question_content is None or question_content == '':
# 获取随机问题内容 (随机数的上限取决于后期配置的模板表的大小)
# 拿到模板表的大小
template_size = 0
sql0 = "select count(*) as counts from `%s`" % "tb_question_content_template"
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0:
template_size = result0['counts']
if template_size < 1:
return False
r_index = randint(1, template_size)
sql0 = "select *from `%s` where content_id=%s" % ("tb_question_content_template", r_index)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0:
question_content = result0['content_value']
# 拿到插入的数据
props = dict()
props['question_username'] = username
props['question_content'] = question_content
props['question_score'] = question_score
props['question_grade'] = grade
props['question_subject'] = subject
props['question_time'] = time_now_str()
props['question_status'] = 0
if options:
assert isinstance(options, dict)
props.update(options)
sql = forEachPlusInsertProps("tb_question", props)
msg = "[in post_question] sql=" + sql
logging.info(msg)
cursor = db_manager.conn_r.cursor()
try:
cursor.execute(sql)
db_manager.conn_r.commit()
except Exception:
db_manager.conn_r.rollback()
raise Exception
# 提问成功, 更新积分
# 更新用户积分
user_operation_type = 2
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points'] - question_score
if point_value >= 0:
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
else:
# 查询是否存在记录
sql_110 = "select *from `%s` where username='******' and point_type=%s" % ("tb_user_points", username,
user_operation_type)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_110)
result_110 = cursor_0.fetchone()
cursor_0.close()
if result_110:
point_value += result_110['point_value']
sql_0 = "update `%s` set point_value=%s where username='******' and point_type=%s" % \
("tb_user_points", point_value, username, user_operation_type)
else:
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s)" % \
("tb_user_points", username, user_operation_type, point_value)
msg_110 = "[in post_question refresh user points] sql_0=" + sql_0
logging.info(msg_110)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True
