示例#1
0
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
示例#2
0
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
示例#3
0
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
示例#4
0
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
示例#5
0
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, ""
示例#6
0
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, "回答成功"
示例#7
0
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