def get_settings_list(self,
pid=None,
name=None,
s_type=None,
user=None,
status=None,
pj_status=None,
page=1,
limit=10):
sql = """
SELECT s.id, s.project project_id, s.type, s.name, s.value, s.status, p.name project_name, s.sort
FROM t_settings s JOIN t_projects p ON s.project=p.id
"""
sql_count = 'SELECT COUNT(*) count FROM t_settings s JOIN t_projects p ON s.project=p.id'
where = []
param = dict()
if pid is not None:
where.append('s.project=%(pid)s')
param['pid'] = pid
if name is not None:
where.append("s.name=%(name)s")
param['name'] = name
if s_type is not None:
where.append("s.type=%(s_type)s")
param['s_type'] = s_type
if user is not None:
where.append("p.user LIKE %(user)s")
param['user'] = '******'.format(user)
if status is not None:
where.append('s.status=%(status)s')
param['status'] = status
if pj_status is not None:
where.append('p.status=%(pj_status)s')
param['pj_status'] = pj_status
if where:
sql += ' WHERE {}'.format(' AND '.join(where))
sql_count += ' WHERE {}'.format(' AND '.join(where))
if s_type == 'report':
sql += ' ORDER BY s.name DESC'
elif s_type == 'job':
sql += ' ORDER BY s.sort, s.status, s.name DESC'
elif s_type == 'host':
sql += ' ORDER BY s.project DESC, s.name DESC, s.value, s.status'
else:
sql += ' ORDER BY s.sort, s.status, s.project DESC, s.name DESC, s.value'
if limit is not None:
offset = (page - 1) * limit
sql += ' LIMIT {},{}'.format(offset, limit)
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor = yield pool.execute(sql_count, param)
total = cursor.fetchone()
cursor.close()
return munchify(result), munchify(total).count
def get_settings_by_range(self,
pid,
s_type,
sort,
range_col='name',
start='',
end=''):
sql = """
SELECT s.id, s.project project_id, s.type, s.name, s.value, s.status, p.name project_name, s.sort
FROM t_settings s JOIN t_projects p ON s.project=p.id
"""
where = []
param = dict()
if pid is not None:
where.append('s.project=%(pid)s')
param['pid'] = pid
if s_type is not None:
where.append("s.type=%(s_type)s")
param['s_type'] = s_type
if sort is not None:
where.append("s.sort=%(sort)s")
param['sort'] = sort
if range_col == 'name':
where.append('s.name BETWEEN %(start)s AND %(end)s')
else:
where.append('s.value BETWEEN %(start)s AND %(end)s')
param['start'] = start
param['end'] = end
if where:
sql += ' WHERE {}'.format(' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor.close()
return munchify(result)
def get_projects_list(self, page=1, limit=10, status=None):
sql = 'SELECT * FROM t_projects p'
param = dict()
if status is not None:
sql += ' WHERE p.status=%(status)s'
param['status'] = status
sql += ' ORDER BY p.id DESC'
if limit is not None:
offset = (page - 1) * limit
sql += ' LIMIT {},{}'.format(offset, limit)
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor = yield pool.execute('SELECT COUNT(*) count FROM t_projects')
total = cursor.fetchone()
cursor.close()
return munchify(result), munchify(total).count
def get_users_list(self, page=1, limit=10, status=None):
sql = 'SELECT * FROM t_users u'
sql_count = 'SELECT COUNT(*) count FROM t_users u'
if status is not None:
sql += ' WHERE u.status=%(status)s'
sql_count += ' WHERE u.status=%(status)s'
sql += ' ORDER BY u.role'
if limit is not None:
offset = (page - 1) * limit
sql += ' LIMIT {},{}'.format(offset, limit)
cursor = yield pool.execute(sql, dict(status=status))
result = cursor.fetchall()
cursor = yield pool.execute(sql_count, dict(status=status))
total = cursor.fetchone()
cursor.close()
return munchify(result), munchify(total).count
def edit_user(self,
email,
password=None,
username=None,
nickname=None,
last_login_time=None,
role=None,
status=None):
user = yield self.get_user_info(email)
if user:
update = []
param = dict(email=email)
if password is not None:
update.append("password=%(password)s")
param['password'] = self.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 nickname is not None:
update.append("nickname=%(nickname)s")
param['nickname'] = nickname
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 get_setting_by_id(self, sid):
sql = """
SELECT s.id, s.project project_id, s.type, s.name, s.value, s.status, p.name project_name, s.sort
FROM t_settings s JOIN t_projects p ON s.project=p.id WHERE s.id=%(sid)s
"""
cursor = yield pool.execute(sql, dict(sid=sid))
result = cursor.fetchone()
cursor.close()
return munchify(result)
def get_user_info(self, email_or_username, status=None):
sql = "SELECT * FROM t_users u WHERE (u.email=%(user)s OR u.username=%(user)s)"
param = dict(user=email_or_username)
if status is not None:
sql += ' AND u.status=%(status)s'
param['status'] = status
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
def get_projects_by_user(self, user, status=None):
sql = "SELECT * FROM t_projects p WHERE p.user LIKE %(user)s"
param = dict(user='******'.format(user))
if status is not None:
sql += ' AND p.status=%(status)s'
param['status'] = status
sql += ' ORDER BY p.id DESC'
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor.close()
return munchify(result)
def get_settings_by_ids(self, sids):
if sids and isinstance(sids, list):
param = dict()
ins = ''
for i in range(len(sids)):
param['id{}'.format(i)] = sids[i]
ins += '%(id{})s,'.format(i)
sql = """
SELECT s.id, s.project project_id, s.type, s.name, s.value, s.status, p.name project_name, s.sort
FROM t_settings s JOIN t_projects p ON s.project=p.id WHERE s.id in ({}) ORDER BY s.sort
""".format(ins[:-1])
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor.close()
return munchify(result)
else:
return []
def get_users_info_by_ids(self, ids, status=None):
if ids and isinstance(ids, list):
param = dict()
ins = ''
for i in range(len(ids)):
param['id{}'.format(i)] = ids[i]
ins += '%(id{})s,'.format(i)
sql = 'SELECT * FROM t_users u WHERE u.id in ({})'.format(ins[:-1])
if status is not None:
sql += ' AND status=%(status)s'
param['status'] = status
sql += ' ORDER BY u.role'
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor.close()
return munchify(result)
else:
return []
def register_user(self, email, password):
register_time = time.strftime('%Y-%m-%d %H:%M:%S')
password = self.func.encode_password(password)
cursor = yield pool.execute('SELECT COUNT(*) count FROM t_users')
total = munchify(cursor.fetchone())
if total.count == 0:
role = 0
else:
role = 2
username = '******'.format(
email.split('@')[0], str(int(time.time() * 1000)))
sql = """
INSERT INTO t_users (username, email, password, registerTime, lastLoginTime, role)
VALUE(%(username)s, %(email)s, %(password)s, %(registerTime)s, %(lastLoginTime)s, %(role)s)
"""
user = yield self.get_user_info(email_or_username=email)
if not user:
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
yield cursor.execute(
sql,
dict(username=username,
email=email,
password=password,
registerTime=register_time,
lastLoginTime=register_time,
role=role))
except pymysql.Error as e:
yield conn.rollback()
log.error('注册用户失败#{}'.format(e))
flag, msg = False, '注册用户失败#{}'.format(e)
else:
yield conn.commit()
log.info('注册用户成功')
flag, msg = cursor.lastrowid, '注册用户成功'
else:
log.error('该用户已存在, 请更换注册邮箱')
flag, msg = False, '该用户已存在, 请更换注册邮箱'
return flag, msg
def get_project(self, name=None, pid=None, status=None):
where = []
param = dict()
if status is not None:
where.append("p.status=%(status)s")
param['status'] = status
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
sql = 'SELECT * FROM t_projects p'
if where:
sql += ' WHERE {}'.format(' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
else:
log.error('参数不对, 获取项目失败')
return None