예제 #1
0
def set_task(projectid, task_title, task_description, budget):
    """
    Create a task

        :param projectid: The corresponding project id
        :param task_title: The title of the task
        :param task_description: The description of the task
        :param budget: The task budget
        :type projectid: str
        :type task_title: str
        :type task_description: str
        :type budget: str
    """
    db.connect()
    cursor = db.cursor()
    query = (
        "INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (%s, %s, %s, %s, %s)"
    )
    try:
        cursor.execute(query, (projectid, task_title, task_description, budget,
                               "waiting for delivery"))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #2
0
def set_projects_user(projectid,
                      userid,
                      read_permission="TRUE",
                      write_permission="NULL",
                      modify_permission="NULL"):
    """
    Add a user to a project with specific permissions
        :param projectid: The project id
        :param userid: The user id
        :param read_permission: Describes whether a user can view information about a project
        :param write_permission: Describes whether a user can add files to tasks
        :param modify_permission: Describes wheter a user can deliver tasks
        :type projectid: str
        :type userid: str
        :type read_permission: str
        :type write_permission: str
    """
    db.connect()
    cursor = db.cursor()
    # query = ("INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)")
    query = ("INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)")
    try:
        # cursor.execute(query, (projectid, userid, read_permission, write_permission, modify_permission))
        cursor.execute(
            query,
            (projectid, userid, read_permission == "TRUE", write_permission
             == "TRUE", modify_permission == "TRUE"))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #3
0
def set_task_file(taskid, filename):
    """
    Register a new task - file relationship

        :param taskid: The task id
        :param filename: The name of the file
        :type taskid: str
        :type filename: str
    """
    db.connect()
    cursor = db.cursor(prepared=True)
    sql_cmd = """INSERT INTO task_files (taskid, filename) VALUES (%s, %s)"""
    sql_value = (
        taskid,
        filename,
    )
    #query = ("INSERT INTO task_files (taskid, filename) VALUES (\"" +
    #    taskid + "\", \"" + filename + "\")")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg("set_task_file: {}".format(sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query set_task_file: {}".format(err))
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #4
0
def update_project_status(projectid, status):
    """
    Change the status of a selected project
        :param projectid: The project id
        :param status: The status to change to, should be either open, in progress or finished
        :type projectid: str
        :type status: str
    """
    db.connect()
    cursor = db.cursor(prepared=True)
    sql_cmd = """UPDATE projects SET project_status = %s WHERE projectid = %s"""
    sql_value = (
        status,
        projectid,
    )
    #query = ("UPDATE projects SET project_status = \"" + status +
    #    "\" WHERE projectid = \"" + projectid + "\"")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg(
            "update_project_status_input: {}".format(sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query update_project_status: {}".format(err))
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #5
0
def set_project(categoryid, userid, project_title, project_description,
                project_status):
    """
    Store a project in the database

        :param categoryid: The id of the corresponding category
        :param userid: The id of the project owner
        :param project_title: The title of the project
        :param project_description: The project description
        :param project_status: The status of the project
        :type categoryid: str
        :type userid: str
        :type project_title: str
        :type project_description: str 
        :type project_status: str
        :return: The id of the new project
    """
    db.connect()
    cursor = db.cursor()
    query = ("INSERT INTO projects VALUES (NULL, %s, %s, %s, %s, %s)")
    try:
        cursor.execute(query, (categoryid, userid, project_title,
                               project_description, project_status))
        db.commit()
        users_projects = get_projects_by_owner(userid)
        projectid = users_projects[-1][0]
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        projectid = None
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
    return projectid
예제 #6
0
def update_user_password(username, password, ip, fullpath):
    """
    Update user's password

        :param username: The user attempting to authenticate
        :param password: The new corresponding password
        :type username: str
        :type password: str
        :return: user
    """
    db.connect()
    cursor = db.cursor(prepared=True)

    sql_cmd = """UPDATE users SET password=%s WHERE username = %s """
    sql_value = (
        password,
        username,
    )

    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg(
            "A user success update_user_password:{}-{}-{}".format(
                ip, fullpath, sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query update_user_password: {}".format(err))
        print("Failed executing query update_user_password: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
    return
예제 #7
0
def update_task_status(taskid, status):
    db.connect()
    cursor = db.cursor()
    query = ("UPDATE tasks SET task_status = %s WHERE taskid = %s")
    try:
        cursor.execute(query, (status, taskid))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #8
0
def set_project(categoryid, userid, project_title, project_description,
                project_status):
    """
    Store a project in the database

        :param categoryid: The id of the corresponding category
        :param userid: The id of the project owner
        :param project_title: The title of the project
        :param project_description: The project description
        :param project_status: The status of the project
        :type categoryid: str
        :type userid: str
        :type project_title: str
        :type project_description: str 
        :type project_status: str
        :return: The id of the new project
    """
    #using mysql placeholder
    # reference: https://pymysql.readthedocs.io/en/latest/user/examples.html
    db.connect()
    cursor = db.cursor(prepared=True)
    sql_cmd = "INSERT INTO projects VALUES (NULL, %s, %s, %s, %s, %s)"
    sql_value = (
        categoryid,
        userid,
        project_title,
        project_description,
        project_status,
    )
    #query = ("INSERT INTO projects VALUES (NULL, \"" +
    #    categoryid + "\", \"" + userid + "\", \"" + project_title + "\", \"" +
    #    project_description + "\", \"" + project_status + "\")")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg("set_porject_input: {}".format(sql_value))
        db.commit()
        users_projects = get_projects_by_owner(userid)
        projectid = users_projects[-1][0]
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query set_porject_input: {}".format(err))
        print("Failed executing query: {}".format(err))
        projectid = None
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
    return projectid
예제 #9
0
def verify_user_by_email(verification_key):
    is_real_key = match_verification_key(verification_key)

    db.connect()
    cursor = db.cursor()
    query = "UPDATE users SET verified = 1 WHERE verification_key = %s"

    try:
        cursor.execute(query, (verification_key, ))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #10
0
def set_projects_user(projectid,
                      userid,
                      read_permission="TRUE",
                      write_permission="NULL",
                      modify_permission="NULL"):
    """
    Add a user to a project with specific permissions
        :param projectid: The project id
        :param userid: The user id
        :param read_permission: Describes whether a user can view information about a project
        :param write_permission: Describes whether a user can add files to tasks
        :param modify_permission: Describes wheter a user can deliver tasks
        :type projectid: str
        :type userid: str
        :type read_permission: str
        :type write_permission: str
    """
    db.connect()
    cursor = db.cursor(prepared=True)
    sql_cmd = """INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)"""
    sql_value = (
        projectid,
        userid,
        read_permission,
        write_permission,
        modify_permission,
    )
    #query = ("INSERT INTO projects_users VALUES (\"" + projectid + "\", \"" +
    #    userid + "\", " + read_permission + ", " +
    #    write_permission + ", " + modify_permission + ")")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg("set_project_users: {}".format(sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query set_project_users: {}, {}".format(
                err, sql_value))
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #11
0
def update_reset_token(username, token, ip, fullpath):
    """
    Update user's forget token
        :param username: The user attempting to authenticate
        :param email: The user's corresponding email
        :param ip: user's ip address
        :param fullpath: user's accessed file path
        :timestamp: user's accessed time stamp
        :type username: str
        :type email: str
        :param ip: str
        :param fullpath: str
        :timestamp: str
        :return: email
    """

    db.connect()
    cursor = db.cursor(prepared=True)

    sql_cmd = """UPDATE users SET verify_token = %s WHERE username = %s"""
    sql_value = (
        token,
        username,
    )

    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg(
            "A user success update_reset_token:{}-{}-{}".format(
                ip, fullpath, sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query update_reset_token: {}".format(err))
        print("Failed executing query update_reset_token: {}".format(err))
        cursor.fetchall()
        exit(1)
    except Exception as e:
        print("An exception occur during execute update_reset_token {}".format(
            e))
    finally:
        cursor.close()
        db.close()
    return
예제 #12
0
def record_user_login(username, ip, fullpath, access_time):
    """
    Create a lock out function.

        :param username: The user attempting to authenticate
        :type username: str
        :param ip: The user's ip
        :type username: str
        :param fullpath: The user access path
        :type username: str
        :return: times of query
        reference: https://stackoverflow.com/questions/19966123/get-time-interval-in-mysql
    """
    db.connect()
    cursor = db.cursor(prepared=True)
    #print("enter logger!")
    # log the request
    sql_cmd_log = """INSERT INTO user_access_time VALUES (NULL, %s, %s, %s)"""
    #print (sql_cmd_log)
    msg = "A user attempt:IP:{}-{}".format(ip, fullpath)
    #print(len(msg))
    sql_value_log = (
        username,
        msg,
        access_time,
    )

    try:
        cursor.execute(sql_cmd_log, sql_value_log)
        log_input_msg("A user attempt in IP record_user_login:{}-{}-{}".format(
            ip, fullpath, sql_value_log))
        db.commit()
    except mysql.connector.Error as err:
        log_error_msg(
            "Failed executing query record_user_login: {}".format(err))
        print("Failed executing query record_user_login: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
    return
예제 #13
0
def update_project_status(projectid, status):
    """
    Change the status of a selected project
        :param projectid: The project id
        :param status: The status to change to, should be either open, in progress or finished
        :type projectid: str
        :type status: str
    """
    db.connect()
    cursor = db.cursor()
    query = ("UPDATE projects SET project_status = %s WHERE projectid = %s")
    try:
        cursor.execute(query, (status, projectid))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #14
0
def set_task_file(taskid, filename):
    """
    Register a new task - file relationship

        :param taskid: The task id
        :param filename: The name of the file
        :type taskid: str
        :type filename: str
    """
    db.connect()
    cursor = db.cursor()
    query = ("INSERT INTO task_files (taskid, filename) VALUES (%s, %s)")
    try:
        cursor.execute(query, (taskid, filename))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #15
0
파일: register.py 프로젝트: asmundh/Progsek
def set_user(username, password, full_name, company, email, 
        street_address, city, state, postal_code, country, verification_key):
    """
    Register a new user in the database
        :param username: The users unique user name
        :param password: The password
        :param full_name: The users full name
        :param company: The company the user represents
        :param email: The users email address
        :param street_address: The street address of the user
        :param city: The city where the user lives
        :param state: The state where the user lives
        :param postal_code: The corresponding postal code
        :param country: The users country
        :type username: str
        :type password: str
        :type full_name: str
        :type company: str
        :type email: str
        :type street_address: str
        :type city: str
        :type state: str
        :type postal_code: str
        :type country: str
    """
    db.connect()
    cursor = db.cursor()
    query = ("INSERT INTO users VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0)")
    try:
        cursor.execute(query, (username, password, full_name, company, email, street_address, city, state, postal_code, country, verification_key))
        db.commit()
    except mysql.connector.Error as err:
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #16
0
def update_task_status(taskid, status):
    db.connect()
    cursor = db.cursor(prepared=True)
    sql_cmd = """UPDATE tasks SET task_status = %s WHERE taskid = %s"""
    sql_value = (
        status,
        taskid,
    )
    #query = ("UPDATE tasks SET task_status = \"" + status +
    #    "\" WHERE taskid = \"" + taskid + "\"")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg("update_task_status: {}".format(sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query update_task_status: {}".format(err))
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #17
0
def set_task(projectid, task_title, task_description, budget):
    """
    Create a task

        :param projectid: The corresponding project id
        :param task_title: The title of the task
        :param task_description: The description of the task
        :param budget: The task budget
        :type projectid: str
        :type task_title: str
        :type task_description: str
        :type budget: str
    """
    db.connect()
    cursor = db.cursor(prepared=True)
    sql_cmd = """INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (%s, %s, %s, %s, "waiting for delivery")"""
    sql_value = (
        projectid,
        task_title,
        task_description,
        budget,
    )
    #query = ("INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (\"" +
    #    projectid + "\", \"" + task_title + "\", \"" +
    #    task_description + "\", \"" + budget + "\", \"waiting for delivery\")")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg("set_task: {}".format(sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg("Failed executing query set_task: {}".format(err))
        print("Failed executing query: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #18
0
파일: activity.py 프로젝트: kejukeji/pub_py
def collect_or_cancel_activity(user_id, activity_id):
    """
    收藏或者,取消收藏
    """
    user_activity = UserActivity.query.filter(UserActivity.user_id == user_id, UserActivity.activity_id == activity_id).first()
    if user_activity:
        try:
            db.delete(user_activity)
            db.commit()
            return True
        except:
            return False
    else:
        user_activity = UserActivity(user_id=user_id, activity_id=activity_id)
        try:
            db.add(user_activity)
            db.commit()
            user_info = UserInfo.query.filter(UserInfo.user_id == user_id).first()
            user_info.add_credit('activity')
            sender_user = UserInfo.query.filter(UserInfo.user_id == user_id).first()
            sender_user.add_reputation('activity')
            return True
        except:
            return False
예제 #19
0
def update_token_to_null(username, token, ip, fullpath):
    """
    search for user's token
        :param username: The user's name
        :param ip: user's ip address
        :param fullpath: user's accessed file path
        :timestamp: user's accessed time stamp

        :type username: str
        :type email: str
        :param ip: str
        :param fullpath: str

        :return: email
    """

    db.connect()
    cursor = db.cursor(prepared=True)

    sql_cmd = """UPDATE users SET verify_token = Null, temp = 0 WHERE username = %s  AND verify_token = %s"""
    sql_value = (
        username,
        token,
    )

    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg(
            "A user success update_token_to_null:{}-{}-{}".format(
                ip, fullpath, sql_value))
        actions = db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(
            "Failed executing query update_token_to_null: {}".format(err))
        print("Failed executing query update_token_to_null: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
    return actions
예제 #20
0
def set_user(username, password, full_name, company, email, street_address,
             city, state, postal_code, country, ip, path, temp, google_token,
             temp_token):
    """
    Register a new user in the database
        :param username: The users unique user name
        :param password: The password
        :param full_name: The users full name
        :param company: The company the user represents
        :param email: The users email address
        :param street_address: The street address of the user
        :param city: The city where the user lives
        :param state: The state where the user lives
        :param postal_code: The corresponding postal code
        :param country: The users country
        :param ip: The user's ip address
        :param path: The user's access path
        :param temp: The user's account is temporary or not
        :param google_token: The user's google token
        :param temp_token: The user's access token

        :type username: str
        :type password: str
        :type full_name: str
        :type company: str
        :type email: str
        :type street_address: str
        :type city: str
        :type state: str
        :type postal_code: str
        :type country: str
        :type ip: num
        :type path: str
        :type temp: boolean
        :type google_token: str 
        :type temp_token: str
    """
    db.connect()
    cursor = db.cursor(prepared=True)
    # get register date
    current_time = datetime.datetime.today().strftime('%Y-%m-%d')
    #genereate sql command
    sql_cmd = """INSERT INTO users VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
    sql_value = (username, password, full_name, company, email, street_address,
                 city, state, postal_code, country, current_time, temp,
                 google_token, temp_token)
    #query = ("INSERT INTO users VALUES (NULL, \"" + username + "\", \"" +
    #    password + "\", \"" + full_name + "\" , \"" + company + "\", \"" +
    #    email + "\", \"" + street_address + "\", \"" + city + "\", \"" +
    #    state  + "\", \"" + postal_code + "\", \"" + country + "\")")
    try:
        cursor.execute(sql_cmd, sql_value)
        logger.log_input_msg("register:IP:{}-{}-{}".format(
            ip, path, sql_value))
        db.commit()
    except mysql.connector.Error as err:
        logger.log_error_msg(err)
        print("Failed executing query register-set_user: {}".format(err))
        cursor.fetchall()
        exit(1)
    finally:
        cursor.close()
        db.close()
예제 #21
0
    def handle(self, message={}):
        """
        Handle a received message in login state

        :param message:
        :return:
        """
        try:
            BaseController.handle(self, message)
        except KeyError:
            key = message.get("k")
            value = message.get("v")
            if key != "msg" or len(value) == 0:
                return

            # State - Login username
            if self.state == State.login_username:
                account = Account.get(lambda account: account.name.lower() == value.lower())

                if account:
                    self.account_id = account.id
                    self.request_password()
                    self.state = State.login_password
                else:
                    self.send_offtopic("Create new account with this name y/n?")
                    self.account_name = value
                    self.state = State.create_account

            # State - Login password
            elif self.state == State.login_password:
                account = Account[self.account_id]

                if self.hash_password_with_salt(account.password, self.dynamic_salt) == value:
                    self.send_offtopic("Login OK")
                    # Set controller to menu controller
                    self.connection.controller = MenuController(self.connection, self.runtime, self.account_id)
                    return

                else:
                    logging.info("Expected password: {}".format(
                        self.hash_password_with_salt(account.password, self.dynamic_salt)))
                    logging.info("Received password: {}".format( value))
                    self.send_offtopic("Login fail")
                    self.state = State.login_username
                    self.greeting()

            # State - Create account
            elif self.state == State.create_account:
                if value.lower()[0] == "y":
                    self.request_password(True, False)
                    self.state = State.create_password
                else:
                    self.greeting()
                    self.state = State.login_username

            # State - Create account - password
            elif self.state == State.create_password:
                self.password = value
                logging.info("Received password 1: {}".format(value))

                self.request_password(False)
                self.state = State.create_password_repeat

            # State - Create account - password repeat
            elif self.state == State.create_password_repeat:
                dynamic_password = self.hash_password_with_salt(self.password, self.dynamic_salt)
                logging.info("Received password 2: {}".format(value))

                if dynamic_password == value:
                    self.state = State.login_username
                    self.send_offtopic("New account has been created, you may now login.")
                    self.greeting()
                    account = Account(name=self.account_name,
                                      password=self.password,
                                      salt=self.static_salt)
                    db.commit()

                else:
                    self.send_offtopic("Password mismatch. Account not created.")
                    self.greeting()
                    self.state = State.login_username