def follow_other(username, other_username):
"""
关注某人
:param username: 用户名
:param other_username: 其他的用户名
:return:
"""
db_manager = DBManager()
# 校验两者关系
sql0 = "select *from `%s` where (username='******' and other_username='******' and relation_type=0) or (username='******' " \
"and other_username='******' and relation_type=1)" % ("tb_relation", username, other_username, other_username,
username)
msg = "[in follow_other] sql0=" + sql0
logging.info(msg)
cursor0 = db_manager.conn_r.cursor()
result0 = cursor0.execute(sql0)
cursor0.close()
logging.info(result0)
if result0 is not None and result0 != 0:
return False
props = dict()
props['username'] = username
props['other_username'] = other_username
props['relation_type'] = 0
sql = forEachPlusInsertProps("tb_relation", props)
try:
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False
# 插入反向关系
props = dict()
props['username'] = other_username
props['other_username'] = username
props['relation_type'] = 1
sql = forEachPlusInsertProps("tb_relation", props)
try:
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True
def feedback(username, content, feed_time):
"""
意见反馈
:param username: 用户名
:param content: 反馈内容
:param feed_time: 提交反馈的时间
:return:
"""
db_manager = DBManager()
props = dict()
props['username'] = username
props['content'] = content
props['feed_time'] = feed_time
sql = forEachPlusInsertProps("tb_feedback", props)
try:
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False
return True
def sign_up(username, sign_time):
"""
每日签到
:param username: 用户名
:param sign_time: 签到时间
:return:
"""
db_manager = DBManager()
# 验证用户当天是否已经签到过
sql0 = "select *from `%s` where username='******' and to_days(sign_time)=to_days('%s')" % ("tb_sign", username,
string_toDatetime(sign_time))
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is not None:
return False, "今天已签到过", None
props = dict()
props['username'] = username
props['sign_time'] = sign_time
sql1 = forEachPlusInsertProps("tb_sign", props)
try:
cursor1 = db_manager.conn_r.cursor()
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, "签到失败", None
# 获取系统赠送的积分
user_operation_type = 6
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
return False, "签到失败", None
db_manager.close()
msg0 = "[in sign_up] sql_0=" + sql_0
logging.info(msg0)
return True, "签到成功", point_value
def register(username, password, grade, identifier, nickname, subject, serial_number, options=None):
"""
用户注册 (用户获取登录账户的唯一途径)
1. 学生注册: username, password, grade为必填项, invitation_code为选填项
2. 教师注册: username, password, grade, subject, serial_number为必填项
:param username: 用户名 (需要检查唯一性)
:param password: 密码
:param grade: 年级
:param identifier: 用户类型 (0: 学生 1: 教师)
:param subject: 科目 (教师才有该选项)
:param serial_number: 教师证 (教师才有)
:param options: 可变参数 (由user_type决定)
:return:
"""
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor()
# 判断用户名是否存在
sql = "select * from `%s` where username='******'" % ("tb_account", username)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
if result:
db_manager.close()
return False
# 键值对
prop_dict = dict()
prop_dict['username'] = username
prop_dict['identifier'] = identifier
prop_dict['grade'] = grade
prop_dict['nickname'] = nickname
if subject is not None:
prop_dict['subject'] = subject
if serial_number is not None:
prop_dict['serial_number'] = serial_number
if options:
assert isinstance(options, dict)
prop_dict.update(options)
insert_sql = forEachPlusInsertProps('tb_user', prop_dict)
msg0 = "[in register] insert_sql=" + insert_sql
logging.info(msg0)
cursor1 = db_manager.conn_r.cursor()
try:
cursor1.execute(insert_sql)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
password = hashlib.sha224(password).hexdigest()
sql0 = "insert into `%s` (%s, %s) values ('%s', '%s')" % ("tb_account", "username", "password", username, password)
msg = "[in register] sql=" + sql0
logging.info(msg)
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()
db_manager.close()
return True
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 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
