def get_statistics_list(self,
s_type=None,
user_id=None,
project_id=None,
name=None,
status=None,
search=None,
page=1,
limit=None,
group_by=list(),
start_time=None,
end_time=None,
order_by=list()):
where = []
param = dict()
sql = '''SELECT p.name projectName, s.projectId, s.name, s.id, p.config, s.value
FROM t_statistics s JOIN t_projects p ON p.id=s.projectId'''
if s_type is not None:
where.append("s.type=%(type)s")
param['type'] = s_type
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
if status is not None:
where.append("s.status=%(status)s")
param['status'] = status
if search is not None:
where.append(
'(p.name LIKE %(search)s OR p.config LIKE %(search)s)')
param['search'] = '%{}%'.format(search)
if start_time and end_time:
where.append(
"s.createTime BETWEEN '{} 00:00:00' AND '{} 23:59:59'".format(
start_time, end_time))
if where:
sql += " WHERE {}".format(' AND '.join(where))
if group_by:
sql += ' GROUP BY {}'.format(','.join(group_by))
count_sql = "SELECT COUNT(*) count FROM ({}) as tmp".format(sql)
if order_by:
sql += ' ORDER BY {}'.format(','.join(order_by))
if limit is not None:
offset = (page - 1) * limit
sql += ' LIMIT {},{}'.format(offset, limit)
try:
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor = yield pool.execute(count_sql, param)
total = cursor.fetchone()
cursor.close()
return munchify(result), munchify(total).count
except pymysql.Error as e:
log.error(e)
return [], 0
def get_users_list(self, page=1, limit=None, status=None, name=None):
sql = 'SELECT * FROM t_users u'
sql_count = 'SELECT COUNT(*) count FROM t_users u'
where = []
params = dict()
if name is not None:
where.append(
"(u.realname like %(name)s OR u.username like %(name)s OR u.email like %(name)s OR u.profile like %(name)s)"
)
params['name'] = '%{}%'.format(name)
if status is not None:
where.append("u.status=%(status)s")
params['status'] = status
if where:
where = ' WHERE {}'.format(' AND '.join(where))
sql += where
sql_count += where
sql += ' ORDER BY u.role'
if limit is not None:
offset = (page - 1) * limit
sql += ' LIMIT {},{}'.format(offset, limit)
try:
cursor = yield pool.execute(sql, params)
result = cursor.fetchall()
cursor = yield pool.execute(sql_count, params)
total = cursor.fetchone()
cursor.close()
return munchify(result), munchify(total).count
except pymysql.Error as e:
log.error(e)
return [], 0
def get_options_list(self, o_type=None, name=None, status=None):
where = []
param = dict()
if o_type is not None:
where.append("o.type=%(type)s")
param['type'] = o_type
if name is not None:
where.append("o.name=%(name)s")
param['name'] = name
if status is not None:
where.append("o.status=%(status)s")
param['status'] = status
try:
if where:
sql = "SELECT * FROM t_options o WHERE {}".format(
' AND '.join(where))
cursor = yield pool.execute(sql, param)
else:
sql = "SELECT * FROM t_options"
cursor = yield pool.execute(sql)
result = cursor.fetchall()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return []
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_message(self, m_type=None, user_id=None, mid=None, status=None):
where = []
param = dict()
if m_type is not None:
where.append("m.type=%(type)s")
param['type'] = m_type
if user_id is not None:
where.append("m.userId=%(userId)s")
param['userId'] = user_id
if mid is not None:
where.append("m.id=%(mid)s")
param['mid'] = mid
if status is not None:
where.append("m.status=%(status)s")
param['status'] = status
if where:
try:
sql = "SELECT m.*,u.username,u.realname,u.email,u.`profile`,u.role,u.`status` as userStatus,u.registerTime FROM t_messages m JOIN t_users u on u.id=m.userId WHERE {}".format(
' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return None
else:
log.error('参数不对, 获取动态消息失败')
return None
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_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 statistics(self,
s_type=None,
p_type=None,
user_id=None,
project_id=None,
name=None,
fields=list(),
status=None,
group_by=list(),
start_time=None,
end_time=None,
order_by=list(),
join='LEFT'):
where = []
param = dict()
if s_type is not None:
where.append("s.type=%(s_type)s")
param['s_type'] = s_type
if p_type is not None:
where.append("p.type=%(p_type)s")
param['p_type'] = p_type
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
if status is not None:
where.append("s.status=%(status)s")
where.append("p.status=%(status)s")
param['status'] = status
if start_time and end_time:
where.append(
"s.createTime BETWEEN '{} 00:00:00' AND '{} 23:59:59'".format(
start_time, end_time))
if where:
if fields:
sql = "SELECT {} FROM t_statistics s {} JOIN t_projects p ON p.id=s.projectId LEFT JOIN t_users u on u.id=s.userId WHERE {}".format(
', '.join(fields), join, ' AND '.join(where))
else:
sql = "SELECT p.id pid, p.`name` projectName,s.id sid, s.type,s.`name`, s.createTime, u.id uid, u.realname, count(s.id) count FROM t_statistics s {} JOIN t_projects p ON p.id=s.projectId LEFT JOIN t_users u on u.id=s.userId WHERE {}".format(
join, ' AND '.join(where))
if group_by:
sql += ' GROUP BY {}'.format(','.join(group_by))
if order_by:
sql += ' ORDER BY {}'.format(','.join(order_by))
try:
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return []
else:
log.error('参数不对, 数据统计失败')
return []
def get_option(self, o_type=None, name=None, oid=None, status=None):
where = []
param = dict()
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 oid is not None:
where.append("o.id=%(oid)s")
param['oid'] = oid
if status is not None:
where.append("o.status=%(status)s")
param['status'] = status
if where:
try:
sql = "SELECT * FROM t_options o WHERE {}".format(
' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return None
else:
log.error('参数不对, 获取系统配置失败')
return None
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 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 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 register_user(self,
email,
password,
username=None,
real_name=None,
profile=None,
role=1,
status=1):
register_time = time.strftime('%Y-%m-%d %H:%M:%S')
password = self.common_func.encode_password(password)
try:
cursor = yield pool.execute('SELECT COUNT(*) count FROM t_users')
total = munchify(cursor.fetchone())
if total.count == 0:
role = 0
status = 2
except pymysql.Error as e:
log.error(e)
username = username or '{}_{}'.format(
email.split('@')[0], str(int(time.time() * 1000)))
sql = """
INSERT INTO t_users (username, email, password, realname, profile, registerTime, lastLoginTime, role, status)
VALUE(%(username)s, %(email)s, %(password)s, %(realname)s, %(profile)s, %(registerTime)s, %(lastLoginTime)s, %(role)s, %(status)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,
realname=real_name or '',
registerTime=register_time,
lastLoginTime=register_time,
profile=json.dumps(profile, ensure_ascii=False)
or '',
role=role,
status=status))
except pymysql.Error as e:
yield conn.rollback()
log.error('注册用户 {} 失败#{}'.format(email, e))
flag, msg = False, '注册用户 {} 失败#{}'.format(email, e)
else:
yield conn.commit()
log.info('注册用户 {} 成功'.format(email))
flag, msg = munchify(
dict(id=cursor.lastrowid, status=status,
role=role)), '注册用户成功!'
else:
log.error('该邮箱已注册!')
flag, msg = False, '该邮箱已注册!'
return flag, msg
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_setting(self,
pid=None,
name=None,
s_type=None,
project=None,
status=None,
pj_status=None,
team_id=None,
create_time=None):
sql = """
SELECT s.id, s.projectId project_id, s.type, s.name, s.value, s.status, p.name project_name, p.teamId team_id, s.sort, s.createTime, o.value team
FROM t_settings s JOIN t_projects p ON s.projectId=p.id LEFT JOIN t_options o ON o.id=p.teamId
"""
where = []
param = dict()
if pid is not None:
if isinstance(pid, list):
pid = ','.join([str(p) for p in pid])
where.append('s.projectId IN ({})'.format(pid))
if project is not None:
where.append("p.type='project' AND p.name=%(project)s")
param['project'] = project
if name is not None:
where.append("s.name=%(name)s")
param['name'] = name
if create_time is not None:
where.append('s.createTime LIKE %(createTime)s')
param['createTime'] = '%{}%'.format(create_time)
if s_type is not None:
if isinstance(s_type, list):
s_type = ', '.join(["'{}'".format(t) for t in s_type])
else:
s_type = "'{}'".format(s_type)
where.append('s.type IN ({})'.format(s_type))
if status is not None:
if isinstance(status, list):
status = ','.join([str(s) for s in status])
where.append('s.status IN ({})'.format(status))
if team_id is not None:
if isinstance(team_id, list):
team_id = ','.join([str(t) for t in team_id])
where.append('p.teamId IN ({})'.format(team_id))
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))
try:
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return None
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 custom_statistics(self, sql=''):
if sql:
try:
cursor = yield pool.execute(sql)
result = cursor.fetchall()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return []
else:
log.error('参数不对, 数据统计失败')
return []
def get_messages_list(self,
m_type=None,
user_id=None,
status=None,
offset=0,
limit=5):
where = []
param = dict(offset=offset, limit=limit)
if m_type is not None:
if isinstance(m_type, list):
m_type = ', '.join(["'{}'".format(t) for t in m_type])
else:
m_type = "'{}'".format(m_type)
where.append('m.type IN ({})'.format(m_type))
if user_id is not None:
where.append("m.userId=%(userId)s")
param['userId'] = user_id
if status is not None:
if isinstance(status, list):
status = ','.join([str(s) for s in status])
where.append('m.status IN ({})'.format(status))
try:
if where:
sql = "SELECT m.*,u.username,u.realname,u.email,u.`profile`,u.role,u.`status` as userStatus,u.registerTime FROM t_messages m JOIN t_users u on u.id=m.userId WHERE {} ORDER BY m.createTime DESC LIMIT %(offset)s,%(limit)s".format(
' AND '.join(where))
cursor = yield pool.execute(sql, param)
else:
sql = "SELECT m.*,u.username,u.realname,u.email,u.`profile`,u.role,u.`status` as userStatus,u.registerTime FROM t_messages m JOIN t_users u on u.id=m.userId ORDER BY m.createTime DESC LIMIT %(offset)s,%(limit)s"
cursor = yield pool.execute(sql)
result = cursor.fetchall()
cursor = yield pool.execute(
'SELECT COUNT(*) count FROM t_messages')
total = cursor.fetchone()
cursor.close()
return munchify(result), munchify(total).count
except pymysql.Error as e:
log.error(e)
return [], 0
def get_users_info_by_id(self, uid, status=None):
if isinstance(uid, list):
uid = ','.join([str(u) for u in uid])
sql = 'SELECT * FROM t_users u WHERE u.id in ({})'.format(uid)
param = dict()
if status is not None:
sql += ' AND status=%(status)s'
param['status'] = status
sql += ' ORDER BY u.role'
try:
cursor = yield pool.execute(sql, param)
result = cursor.fetchall()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return []
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 get_option(self, name=None, oid=None):
where = []
param = dict()
if name is not None:
where.append("o.name=%(name)s")
param['name'] = name
if oid is not None:
where.append("o.id=%(oid)s")
param['oid'] = oid
if where:
sql = "SELECT * FROM t_options o WHERE {}".format(
' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
else:
log.error('参数不对, 获取系统配置失败')
return None
def get_settings_by_id(self, sid, order_by=list()):
if isinstance(sid, list):
sid = ','.join([str(s) for s in sid])
if sid:
sql = """
SELECT s.id, s.projectId project_id, s.type, s.name, s.value, s.status, p.name project_name, p.teamId team_id, s.sort, s.createTime, o.value team
FROM t_settings s JOIN t_projects p ON s.projectId=p.id LEFT JOIN t_options o ON o.id=p.teamId WHERE s.id IN ({})
""".format(sid)
if order_by:
sql += ' ORDER BY {}'.format(','.join(order_by))
try:
cursor = yield pool.execute(sql)
result = cursor.fetchall()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return []
else:
return []
def get_statistics(self,
s_type=None,
user_id=None,
project_id=None,
name=None,
sid=None,
status=None):
where = []
param = dict()
if s_type is not None:
where.append("s.type=%(type)s")
param['type'] = s_type
if user_id is not None:
where.append("s.userId=%(userId)s")
param['userId'] = user_id
if sid is not None:
where.append("s.id=%(sid)s")
param['sid'] = sid
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
if status is not None:
where.append("s.status=%(status)s")
param['status'] = status
if where:
try:
sql = "SELECT * FROM t_statistics s WHERE {}".format(
' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return None
else:
log.error('参数不对, 获取统计信息失败')
return None
def get_project(self,
name=None,
pid=None,
team_id=None,
p_type=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
if p_type is not None:
where.append("p.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("p.id IN ({})".format(pid))
if team_id is not None:
where.append("p.teamId=%(teamId)s")
param['teamId'] = team_id
join_type = 'o.type="cate"' if p_type == 'knowledge.book' else 'o.type="teams" AND o.name="team"'
sql = 'SELECT p.*, o.`value` team FROM t_projects p LEFT JOIN t_options o ON o.id=p.teamId AND {}'.format(
join_type)
if where:
try:
sql += ' WHERE {}'.format(' AND '.join(where))
cursor = yield pool.execute(sql, param)
result = cursor.fetchone()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return None
else:
log.error('参数不对, 获取项目失败')
return None
def register_user(self, email, password):
register_time = time.strftime('%Y-%m-%d %H:%M:%S')
password = self.common_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
def get_user_info(self, email_or_username=None, uid=None, status=None):
where = []
params = dict()
if uid is not None:
where.append("u.id=%(uid)s")
params['uid'] = uid
if email_or_username is not None:
where.append("(u.email=%(user)s OR u.username=%(user)s)")
params['user'] = email_or_username
if status is not None:
where.append("u.status=%(status)s")
params['status'] = status
sql = "SELECT u.*, (SELECT COUNT(IF(m.`type` IN ('notice', 'message') AND m.`status` NOT IN (0, 2), m.`type`, NULL)) + COUNT(IF(m.`type` IN ('todo') AND m.`status` NOT IN (0, 5), m.`type`, NULL)) FROM t_messages m WHERE m.type <> 'active' AND m.userId=u.id AND m.`status` <> 0) as unreadCount FROM t_users u"
if where:
sql += ' WHERE {}'.format(' AND '.join(where))
try:
cursor = yield pool.execute(sql, params)
result = cursor.fetchone()
cursor.close()
return munchify(result)
except pymysql.Error as e:
log.error(e)
return None
def get_options_list(self):
sql = "SELECT * FROM t_options"
cursor = yield pool.execute(sql)
result = cursor.fetchall()
cursor.close()
return munchify(result)
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)
