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