def delete_option(self, name=None, oid=None): where = [] param = dict() if name is not None: where.append("name=%(name)s") param['name'] = name if oid is not None: where.append("id=%(oid)s") param['oid'] = oid if where: sql = "DELETE FROM t_options WHERE {}".format(' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('删除系统配置失败#{}'.format(e)) flag, msg = False, '删除系统配置失败#{}'.format(e) else: yield tx.commit() log.info('删除系统配置成功') flag, msg = True, '删除系统配置成功' else: log.error('没有指定系统配置, 删除失败') flag, msg = False, '没有指定系统配置, 删除失败' return flag, msg
def edit_option(self, value, name=None, oid=None): where = [] param = dict(value=value) if name is not None: where.append("name=%(name)s") param['name'] = name elif oid is not None: where.append("id=%(oid)s") param['oid'] = oid else: log.error('参数不对, 编辑系统配置失败') return False, '参数不对, 编辑系统配置失败' option = yield self.get_option(name=name, oid=oid) if where and option: sql = "UPDATE t_options o SET value=%(value)s WHERE {}".format( ' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('系统配置 {} 编辑失败#{}'.format(option.name, e)) flag, msg = False, '系统配置 {} 编辑失败#{}'.format(option.name, e) else: yield tx.commit() log.info('系统配置 {} 编辑成功'.format(option.name)) flag, msg = True, '系统配置 {} 编辑成功'.format(option.name) else: log.error('没有可编辑的系统配置') flag, msg = False, '没有可编辑的系统配置' return flag, msg
def delete_project(self, name=None, pid=None): pj = yield self.get_project(name=name, pid=pid) if pj: sql = "DELETE FROM t_projects" where = [] param = dict() if name is not None: where.append("name=%(name)s") param['name'] = name elif pid is not None: where.append("id=%(pid)s") param['pid'] = pid if where: sql += ' WHERE {}'.format(' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except Exception as e: yield tx.rollback() log.error('删除项目 {} 失败#{}'.format(pj.name, e)) flag, msg = False, '项目 {} 有关联设置, 请先删除该项目下的所有设置后再删除该项目'.format( pj.name) else: yield tx.commit() log.info('删除项目 {} 成功'.format(pj.name)) flag, msg = True, '删除项目 {} 成功'.format(pj.name) else: flag, msg = False, '请指定删除项目条件, 不能删除所有项目' else: log.error('没有指定删除的项目') flag, msg = False, '没有指定删除的项目' return flag, msg
def edit_project(self, name=None, pid=None, user=None, status=None): update = [] where = [] param = dict() if name is not None: where.append("p.name=%(name)s") param['name'] = name elif pid is not None: where.append("p.id=%(pid)s") param['pid'] = pid if user is not None: update.append("p.user=%(user)s") param['user'] = user if status is not None: update.append("p.status=%(status)s") param['status'] = status pj = yield self.get_project(name=name, pid=pid) if where and update and pj: sql = 'UPDATE t_projects p SET {} WHERE {}'.format( ', '.join(update), ' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: log.error('项目 {} 编辑失败#{}'.format(pj.name, e)) yield tx.rollback() flag, msg = False, '项目 {} 编辑失败#{}'.format(pj.name, e) else: yield tx.commit() log.info('项目 {} 编辑成功'.format(pj.name)) flag, msg = True, '项目 {} 编辑成功'.format(pj.name) return flag, msg else: log.error('编辑项目参数不对') return False, '编辑项目参数不对'
def delete_user(self, uid=None, username_or_email=None, status=None): user = yield self.get_user_info(uid=uid, email_or_username=username_or_email, status=status) if user: where = [] params = dict() if uid is not None: where.append("id=%(uid)s") params['uid'] = uid if username_or_email is not None: where.append("(email=%(user)s OR username=%(user)s)") params['user'] = username_or_email if status is not None: where.append("status=%(status)s") params['status'] = status sql = 'DELETE FROM t_users' if where: sql += ' WHERE {}'.format(' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, params) except pymysql.Error as e: yield tx.rollback() log.error('删除用户失败#{}'.format(e)) flag, msg = False, '删除用户失败' else: yield tx.commit() log.info('删除用户成功') flag, msg = True, '删除用户成功' return flag, msg else: log.error('用户不存在!') return False, '用户不存在!'
def delete_settings_by_type(self, s_type, name=None, status=None): setting = yield self.get_settings_list(s_type=s_type, status=status, name=name) if setting: where = [] param = dict(s_type=s_type) if status is not None: where.append('status=%(status)s') param['status'] = status elif name is not None: where.append('name=%(name)s') param['name'] = name sql = 'DELETE FROM t_settings WHERE type=%(s_type)s' if where: sql += ' AND {}'.format(' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('批量删除设置项 {} 失败#{}'.format(s_type, e)) flag, msg = False, '批量删除设置项 {} 失败#{}'.format(s_type, e) else: yield tx.commit() log.info('批量删除设置项 {} 成功'.format(s_type)) flag, msg = True, '批量删除设置项 {} 成功'.format(s_type) else: flag, msg = False, '不存在指定的设置项' return flag, msg
def delete_message(self, m_type=None, user_id=None, mid=None): where = [] param = dict() if m_type is not None: where.append("type=%(type)s") param['type'] = m_type if user_id is not None: where.append("userId=%(userId)s") param['userId'] = user_id if mid is not None: where.append("id=%(mid)s") param['mid'] = mid if where: sql = "DELETE FROM t_messages WHERE {}".format(' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('删除动态消息失败#{}'.format(e)) flag, msg = False, '删除动态消息失败#{}'.format(e) else: yield tx.commit() log.info('删除动态消息成功') flag, msg = True, '删除动态消息成功' else: log.error('没有指定动态消息, 删除失败') flag, msg = False, '没有指定动态消息, 删除失败' return flag, msg
def edit_user(self, email, password=None, username=None, realname=None, last_login_time=None, role=None, status=None): user = yield self.get_user_info(email) if user: update = [] param = dict(email=user.email) if password is not None: update.append("password=%(password)s") param['password'] = self.common_func.encode_password(password) if username is not None: sql = "SELECT username FROM t_users u WHERE u.email != %(email)s AND u.username = %(username)s" param['username'] = username cursor = yield pool.execute(sql, param) user_info = cursor.fetchone() if user_info: log.error('用户名 {} 已存在'.format(username)) return False, '用户名 {} 已存在'.format(username) else: update.append("username=%(username)s") if realname is not None: update.append("realname=%(realname)s") param['realname'] = realname if last_login_time is not None: update.append("lastLoginTime=%(lastLoginTime)s") param['lastLoginTime'] = last_login_time if role is not None: update.append('role=%(role)s') param['role'] = role if status is not None: update.append('status=%(status)s') param['status'] = status if update: sql = "UPDATE t_users SET {} WHERE email=%(email)s".format( ', '.join(update)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('编辑用户失败#{}'.format(e)) flag, msg = False, '用户 {} 资料修改失败'.format(email) else: yield tx.commit() log.info('用户 {} 资料修改成功'.format(email)) flag, msg = True, '用户 {} 资料修改成功'.format(email) return flag, msg else: log.error('没有可更新的项') return False, '没有可更新的项' else: log.error('没有可编辑的用户#{}'.format(email)) return False, '没有可编辑的用户#{}'.format(email)
def edit_setting(self, sid, status=None, name=None, pid=None, project=None, value=None, sort=None, create_time=None): if project is not None: pid = "SELECT p.id FROM t_projects p WHERE p.`name`='{}' and p.type='project'".format( project) update = [] param = dict(sid=sid) if status is not None: update.append('s.status=%(status)s') param['status'] = status if name is not None: update.append("s.name=%(name)s") param['name'] = name if value is not None: update.append("s.value=%(value)s") param['value'] = json.dumps(value, ensure_ascii=False) if pid is not None: update.append("s.projectId=(%(pid)s)") param['pid'] = pid if sort is not None: update.append('s.sort=%(sort)s') param['sort'] = sort if create_time is not None: update.append('s.createTime=%(createTime)s') param['createTime'] = create_time setting = yield self.get_settings_by_id(sid=sid) if update and setting: sql = 'UPDATE t_settings s SET {} WHERE id=%(sid)s'.format( ', '.join(update)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('更新 {} 配置项 {} 失败#{}'.format( setting[0].type, [x.name for x in setting], e)) flag, msg = False, '更新 {} 配置项 {} 失败#{}'.format( setting[0].type, [x.name for x in setting], e) else: yield tx.commit() log.info('更新 {} 配置项 {} 成功'.format(setting[0].type, [x.name for x in setting])) flag, msg = True, '更新 {} 配置项 {} 成功'.format( setting[0].type, [x.name for x in setting]) return flag, msg else: log.error('没有指定编辑的配置项') return False, '没有指定编辑的配置项'
def edit_message(self, m_type=None, user_id=None, mid=None, content=None, status=None): where = [] update = ["m.updateTime=%(updateTime)s"] param = dict(updateTime=time.strftime('%Y-%m-%d %H:%M:%S')) if m_type is not None: where.append("m.type=%(type)s") param['type'] = m_type if mid is not None: where.append("m.id=%(mid)s") param['mid'] = mid if user_id is not None: where.append("m.userId=%(userId)s") param['userId'] = user_id if status is not None and content is None: update.append("m.status=%(status)s") param['status'] = status elif content is not None and status is None: update.append("m.content=%(content)s") param['content'] = json.dumps(content, ensure_ascii=False) elif status is not None and content is not None: where.append("m.status=%(status)s") update.append("m.content=%(content)s") param['status'] = status param['content'] = json.dumps(content, ensure_ascii=False) message = yield self.get_message(m_type=m_type, user_id=user_id, mid=mid) if where and message: sql = "UPDATE t_messages m SET {} WHERE {}".format( ', '.join(update), ' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('动态消息 #{} 编辑失败#{}'.format(message.id, e)) flag, msg = False, '动态消息 #{} 编辑失败#{}'.format(message.id, e) else: yield tx.commit() log.info('动态消息 #{} 编辑成功'.format(message.id)) flag, msg = True, '动态消息 #{} 编辑成功'.format(message.id) else: log.error('没有可编辑的动态消息') flag, msg = False, '没有可编辑的动态消息' return flag, msg
def edit_statistics(self, status, s_type=None, user_id=None, project_id=None, name=None, sid=None): where = [] param = dict(status=status) if s_type is not None: where.append("s.type=%(type)s") param['type'] = s_type if sid is not None: where.append("s.id=%(sid)s") param['sid'] = sid if user_id is not None: where.append("s.userId=%(userId)s") param['userId'] = user_id if project_id is not None: where.append("s.projectId=%(projectId)s") param['projectId'] = project_id if name is not None: where.append("s.name=%(name)s") param['name'] = name statistics = yield self.get_statistics(s_type=s_type, user_id=user_id, project_id=project_id, sid=sid, name=None, status=status) if where and statistics: sql = "UPDATE t_statistics s SET status=%(status)s WHERE {}".format( ' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('统计信息 {} 编辑失败#{}'.format(statistics.name, e)) flag, msg = False, '统计信息 {} 编辑失败#{}'.format(statistics.name, e) else: yield tx.commit() log.info('统计信息 {} 编辑成功'.format(statistics.name)) flag, msg = True, '统计信息 {} 编辑成功'.format(statistics.name) else: log.error('没有可编辑的统计信息') flag, msg = False, '没有可编辑的统计信息' return flag, msg
def delete_project(self, name=None, team_id=None, p_type=None, pid=None, status=None): pj = yield self.get_project(name=name, pid=pid, p_type=p_type) if pj: sql = "DELETE FROM t_projects" where = [] param = dict() if name is not None: where.append("name=%(name)s") param['name'] = name if p_type is not None: where.append("type=%(type)s") param['type'] = p_type if pid is not None: if isinstance(pid, list): pid = ','.join([str(p) for p in pid]) where.append("id IN ({})".format(pid)) if team_id is not None: where.append("teamId=%(teamId)s") param['teamId'] = team_id if status is not None: where.append("status=%(status)s") param['status'] = status if where: sql += ' WHERE {}'.format(' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except Exception as e: yield tx.rollback() log.error('删除项目 {} 失败#{}'.format(pj.name, e)) flag, msg = False, '删除项目 {} 失败#{}'.format(pj.name, e) else: yield tx.commit() log.info('删除项目 {} 成功'.format(pj.name)) flag, msg = True, '删除项目 {} 成功'.format(pj.name) else: flag, msg = False, '请指定删除项目条件, 不能删除所有项目' else: log.error('没有指定删除的项目') flag, msg = False, '没有指定删除的项目' return flag, msg
def edit_project(self, pid, p_type=None, name=None, team_id=None, config=None, status=None, create_time=None): update = [] param = dict(pid=pid) if name is not None: update.append("p.name=%(name)s") param['name'] = name if team_id is not None: update.append("p.teamId=%(teamId)s") param['teamId'] = team_id if config is not None: update.append("p.config=%(config)s") param['config'] = json.dumps(config, ensure_ascii=False) if status is not None: update.append("p.status=%(status)s") param['status'] = status if create_time is not None: update.append("p.createTime=%(createTime)s") param['createTime'] = create_time pj = yield self.get_project(pid=pid, p_type=p_type) if update and pj: sql = 'UPDATE t_projects p SET {} WHERE p.id=%(pid)s'.format( ', '.join(update)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: log.error('项目 {} 编辑失败#{}'.format(pj.name, e)) yield tx.rollback() flag, msg = False, '项目 {} 编辑失败#{}'.format(pj.name, e) else: yield tx.commit() log.info('项目 {} 编辑成功'.format(pj.name)) flag, msg = True, '项目 {} 编辑成功'.format(pj.name) return flag, msg else: log.error('所编辑项目不存在') return False, '所编辑项目不存在'
def delete_statistics(self, s_type=None, user_id=None, project_id=None, name=None, sid=None): where = [] param = dict() if s_type is not None: where.append("type=%(type)s") param['type'] = s_type if project_id is not None: where.append("projectId=%(projectId)s") param['projectId'] = project_id if user_id is not None: where.append("userId=%(userId)s") param['userId'] = user_id if name is not None: where.append("name=%(name)s") param['name'] = name if sid is not None: where.append("id=%(sid)s") param['sid'] = sid if where: sql = "DELETE FROM t_statistics WHERE {}".format( ' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('删除统计信息失败#{}'.format(e)) flag, msg = False, '删除统计信息失败#{}'.format(e) else: yield tx.commit() log.info('删除统计信息成功') flag, msg = True, '删除统计信息成功' else: log.error('没有指定统计信息, 删除失败') flag, msg = False, '没有指定统计信息, 删除失败' return flag, msg
def delete_settings_list(self, s_type, status=None): setting = yield self.get_settings_list(s_type=s_type, status=status) if setting: if status: sql = 'DELETE FROM t_settings WHERE type=%(s_type)s AND status=%(status)s' else: sql = 'DELETE FROM t_settings WHERE type=%(s_type)s' tx = yield pool.begin() try: yield tx.execute(sql, dict(s_type=s_type, status=status)) except pymysql.Error as e: yield tx.rollback() log.error('批量删除设置项 {} 失败#{}'.format(s_type, e)) flag, msg = False, '批量删除设置项 {} 失败#{}'.format(s_type, e) else: yield tx.commit() log.info('批量删除设置项 {} 成功'.format(s_type)) flag, msg = True, '批量删除设置项 {} 成功'.format(s_type) else: flag, msg = False, '不存在指定的设置项' return flag, msg
def delete_setting(self, sid): setting = yield self.get_setting_by_id(sid=sid) if setting: sql = 'DELETE FROM t_settings WHERE id=%(sid)s' tx = yield pool.begin() try: yield tx.execute(sql, dict(sid=sid)) except pymysql.Error as e: yield tx.rollback() log.error('删除 {} 设置项 {} 失败#{}'.format(setting.type, setting.name, e)) flag, msg = False, '删除 {} 设置项 {} 失败#{}'.format( setting.type, setting.name, e) else: yield tx.commit() log.info('删除 {} 设置项 {} 成功'.format(setting.type, setting.name)) flag, msg = True, '删除 {} 设置项 {} 成功'.format( setting.type, setting.name) else: flag, msg = False, '不存在指定的设置项' return flag, msg
def edit_setting(self, sid, status=None, name=None, value=None, sort=None): update = [] param = dict(sid=sid) if status is not None: update.append('s.status=%(status)s') param['status'] = status if name is not None: update.append("s.name=%(name)s") param['name'] = name if value is not None: update.append("s.value=%(value)s") param['value'] = value if sort is not None: update.append('s.sort=%(sort)s') param['sort'] = sort setting = yield self.get_setting_by_id(sid=sid) if update and setting: sql = 'UPDATE t_settings s SET {} WHERE id=%(sid)s'.format( ', '.join(update)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('更新 {} 配置项 {} 失败#{}'.format(setting.type, setting.name, e)) flag, msg = False, '更新 {} 配置项 {} 失败#{}'.format( setting.type, setting.name, e) else: yield tx.commit() log.info('更新 {} 配置项 {} 成功'.format(setting.type, setting.name)) flag, msg = True, '更新 {} 配置项 {} 成功'.format( setting.type, setting.name) return flag, msg else: log.error('没有指定编辑的配置项') return False, '没有指定编辑的配置项'
def delete_setting(self, sid): setting = yield self.get_settings_by_id(sid=sid) if isinstance(sid, list): sid = ','.join([str(s) for s in sid]) if setting and sid: sql = 'DELETE FROM t_settings WHERE id in ({})'.format(sid) tx = yield pool.begin() try: yield tx.execute(sql) except pymysql.Error as e: yield tx.rollback() log.error('删除 {} 设置项 {} 失败#{}'.format( setting[0].type, [x.name for x in setting], e)) flag, msg = False, '删除 {} 设置项 {} 失败#{}'.format( setting[0].type, [x.name for x in setting], e) else: yield tx.commit() log.info('删除 {} 设置项 {} 成功'.format(setting[0].type, [x.name for x in setting])) flag, msg = True, '删除 {} 设置项 {} 成功'.format( setting[0].type, [x.name for x in setting]) else: flag, msg = False, '不存在指定的设置项' return flag, msg
def edit_option(self, value=None, o_type=None, name=None, oid=None, status=None): where = [] update = [] param = dict() if value is not None: update.append("o.value=%(value)s") if isinstance(value, dict): param['value'] = json.dumps(value, ensure_ascii=False) else: param['value'] = value if o_type is not None: where.append("o.type=%(type)s") param['type'] = o_type if name is not None and oid is None: where.append("o.name=%(name)s") param['name'] = name if name is not None and oid is not None: update.append("o.name=%(name)s") param['name'] = name if oid is not None: where.append("o.id=%(oid)s") param['oid'] = oid if status is not None and value is None: update.append("o.status=%(status)s") param['status'] = status elif status is not None and value is not None: where.append("o.status=%(status)s") param['status'] = status option = yield self.get_option(o_type=o_type, name=name, oid=oid, status=status) if where and update and option: name = option.name o_type = option.type oid = option.id options = [] if o_type == 'teams': option = yield self.get_options_list(o_type=o_type, name=name, status=status) for op in option: if value and json.loads(op.value) == value: options.append(op) oid = op.id if (o_type == 'teams' and not options) or (o_type != 'teams' and option): sql = "UPDATE t_options o SET {} WHERE {}".format( ','.join(update), ' AND '.join(where)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('系统配置 {} 编辑失败#{}'.format(name, e)) flag, msg = False, '系统配置 {} 编辑失败#{}'.format(name, e) else: yield tx.commit() log.info('系统配置 {} 编辑成功'.format(name)) flag, msg = True, '系统配置 {} 编辑成功'.format(name) else: log.error('配置 {} 已存在'.format(value)) flag, msg = oid, '配置 {} 已存在'.format(value) else: log.error('没有可编辑的系统配置') flag, msg = False, '没有可编辑的系统配置' return flag, msg
def edit_user(self, email=None, uid=None, password=None, username=None, real_name=None, last_login_time=None, role=None, status=None, profile=None): user = yield self.get_user_info( email_or_username=None if uid else email, uid=uid) if user: update = [] param = dict(email=email if email else user.email) if password is not None: update.append("password=%(password)s") param['password'] = self.common_func.encode_password(password) if username is not None: sql = "SELECT id, username FROM t_users u WHERE u.email != %(email)s AND u.username = %(username)s" param['username'] = username try: cursor = yield pool.execute(sql, param) user_info = cursor.fetchone() if (user_info and uid and uid != user_info['id']) or (user_info and uid is None): log.error('用户名 {} 已存在'.format(username)) return False, '用户名 {} 已存在'.format(username) else: update.append("username=%(username)s") except pymysql.Error as e: log.error(e) return False, '编辑用户失败#{}'.format(e) if email is not None and uid is not None: is_exist_user = yield self.get_user_info(email) if is_exist_user and is_exist_user.id != uid: log.error('该邮箱 {} 已注册'.format(email)) return False, '该邮箱 {} 已注册'.format(email) else: update.append("email=%(email)s") if real_name is not None: update.append("realname=%(realname)s") param['realname'] = real_name if last_login_time is not None: update.append("lastLoginTime=%(lastLoginTime)s") param['lastLoginTime'] = last_login_time if role is not None: update.append('role=%(role)s') param['role'] = role if profile is not None: update.append('profile=%(profile)s') param['profile'] = json.dumps(profile, ensure_ascii=False) if status is not None: update.append('status=%(status)s') param['status'] = status if update: if uid is not None: param['uid'] = uid sql = "UPDATE t_users SET {} WHERE id=%(uid)s".format( ', '.join(update)) else: sql = "UPDATE t_users SET {} WHERE email=%(email)s".format( ', '.join(update)) tx = yield pool.begin() try: yield tx.execute(sql, param) except pymysql.Error as e: yield tx.rollback() log.error('编辑用户失败#{}'.format(e)) flag, msg = False, '用户 {} 资料修改失败'.format(user.email) else: yield tx.commit() log.info('用户 {} 资料修改成功'.format(user.email)) flag, msg = True, '用户 {} 资料修改成功'.format(user.email) return flag, msg else: log.error('没有可更新的项') return False, '没有可更新的项' else: log.error('用户 {} 不存在!'.format(email)) return False, '用户 {} 不存在!'.format(email)