def list_by_user_id(self,
eid,
user_id,
query="",
page=None,
page_size=None):
limit = ""
if page is not None and page_size is not None:
page = page if page > 0 else 1
page = (page - 1) * page_size
limit = "Limit {page}, {size}".format(page=page, size=page_size)
where = """WHERE a.ID = b.tenant_id
AND c.user_id = b.user_id
AND a.creater = d.user_id
AND b.user_id = {user_id}
AND a.enterprise_id = '{eid}'
""".format(user_id=user_id, eid=eid)
if query:
where += """AND ( a.tenant_alias LIKE "%{query}%" OR d.nick_name LIKE "%{query}%" )""".format(
query=query)
sql = """
SELECT DISTINCT
a.ID,
a.tenant_id,
a.tenant_name,
a.tenant_alias,
a.is_active,
a.enterprise_id,
a.create_time,
d.nick_name as creater
FROM
tenant_info a,
tenant_perms b,
user_info c,
user_info d
{where}
{limit}
""".format(where=where, limit=limit)
print sql
conn = BaseConnection()
result = conn.query(sql)
return result
def get_plugins_by_service_id(self, region, tenant_id, service_id,
category):
QUERY_INSTALLED_SQL = """SELECT tp.plugin_id as plugin_id,tp.desc as "desc",tp.plugin_alias as plugin_alias,tp.category as category,pbv.build_version as build_version,tsp.plugin_status as plugin_status
FROM tenant_service_plugin_relation tsp
LEFT JOIN plugin_build_version pbv ON tsp.plugin_id=pbv.plugin_id AND tsp.build_version=pbv.build_version
JOIN tenant_plugin tp ON tp.plugin_id=tsp.plugin_id
WHERE tsp.service_id="{0}" AND tp.region="{1}" AND tp.tenant_id="{2}" """.format(
service_id, region, tenant_id)
QUERI_UNINSTALLED_SQL = """
SELECT tp.plugin_id as plugin_id,tp.desc as "desc",tp.plugin_alias as plugin_alias,tp.category as category,pbv.build_version as build_version
FROM tenant_plugin AS tp
JOIN plugin_build_version AS pbv ON (tp.plugin_id=pbv.plugin_id)
WHERE pbv.plugin_id NOT IN (
SELECT plugin_id FROM tenant_service_plugin_relation
WHERE service_id="{0}") AND tp.tenant_id="{1}" AND tp.region="{2}" AND pbv.build_status="{3}" """.format(
service_id, tenant_id, region, "build_success")
if category == "analysis":
query_installed_plugin = """{0} AND tp.category="{1}" """.format(
QUERY_INSTALLED_SQL, "analyst-plugin:perf")
query_uninstalled_plugin = """{0} AND tp.category="{1}" """.format(
QUERI_UNINSTALLED_SQL, "analyst-plugin:perf")
elif category == "net_manage":
query_installed_plugin = """{0} AND tp.category in {1} """.format(
QUERY_INSTALLED_SQL, '("net-plugin:down","net-plugin:up")')
query_uninstalled_plugin = """ {0} AND tp.category in {1} """.format(
QUERI_UNINSTALLED_SQL, '("net-plugin:down","net-plugin:up")')
else:
query_installed_plugin = QUERY_INSTALLED_SQL
query_uninstalled_plugin = QUERI_UNINSTALLED_SQL
dsn = BaseConnection()
logger.debug(
"\n query_installed_plugin --- {0} \n query_uninstalled_plugin --- {1}"
.format(query_installed_plugin, query_uninstalled_plugin))
installed_plugins = dsn.query(query_installed_plugin)
uninstalled_plugins = dsn.query(query_uninstalled_plugin)
return installed_plugins, uninstalled_plugins
def get_multi_apps_tags(self, eid, app_ids):
if not app_ids:
return None
app_ids = ",".join("'{0}'".format(app_id) for app_id in app_ids)
sql = """
select
atr.app_id, tag.*
from
console.rainbond_center_app_tag_relation atr
left join console.rainbond_center_app_tag tag on
atr.enterprise_id = tag.enterprise_id
and atr.tag_id = tag.ID
where
atr.enterprise_id = '{eid}'
and atr.app_id in ({app_ids});
""".format(eid=eid, app_ids=app_ids)
conn = BaseConnection()
apps = conn.query(sql)
return apps
def list_by_svc_share_uuids(self, group_id, dep_uuids):
uuids = "'{}'".format("','".join(str(uuid) for uuid in dep_uuids))
conn = BaseConnection()
sql = """
SELECT
a.service_id,
a.service_cname
FROM
tenant_service a,
service_source b,
service_group_relation c
WHERE
a.tenant_id = b.team_id
AND a.service_id = b.service_id
AND b.service_share_uuid IN ( {uuids} )
AND a.service_id = c.service_id
AND c.group_id = {group_id}
""".format(group_id=group_id, uuids=uuids)
result = conn.query(sql)
return result
def get_role_names(self, user_id, tenant_id):
sql = """
SELECT
group_concat( b.role_name ) AS role_names
FROM
tenant_perms a,
tenant_user_role b,
tenant_info c
WHERE
a.role_id = b.ID
AND a.tenant_id = c.ID
AND a.user_id = {user_id}
AND c.tenant_id = '{tenant_id}'""".format(user_id=user_id,
tenant_id=tenant_id)
conn = BaseConnection()
result = conn.query(sql)
if len(result) == 0 or result[0].get("role_names") is None:
raise UserRoleNotFoundException(
"tenant_id: {tenant_id}; user_id: {user_id}; user role not found"
.format(tenant_id=tenant_id, user_id=user_id))
return result[0].get("role_names")
def count_by_tenant_id(self, tenant_id, query=""):
where = """
WHERE
ti.tenant_id = tr.tenant_id
AND ri.region_name = tr.region_name
AND ti.tenant_id = "{tenant_id}"
""".format(tenant_id=tenant_id)
if query:
where += "AND (ri.region_name like '%{query}% OR ri.region_alias like '%{query}%)'".format(
query=query)
sql = """
SELECT
count(*) as total
FROM
region_info ri,
tenant_info ti,
tenant_region tr
{where}
""".format(where=where)
conn = BaseConnection()
result = conn.query(sql)
return result[0]["total"]
def list_users_by_tenant_id(self,
tenant_id,
query="",
page=None,
size=None):
"""
Support search by username, email, phone number
"""
conn = BaseConnection()
limit = ""
if page is not None and size is not None:
page = page if page > 0 else 1
page = (page - 1) * size
limit = "Limit {page}, {size}".format(page=page, size=size)
where = """WHERE a.user_id = b.user_id
AND b.tenant_id = c.ID
AND c.tenant_id = '{tenant_id}'""".format(tenant_id=tenant_id)
if query:
where += """ AND ( a.nick_name LIKE "%{query}%"
OR a.phone LIKE "%{query}%"
OR a.email LIKE "%{query}%" )""".format(query=query)
sql = """
SELECT DISTINCT
a.user_id,
a.email,
a.nick_name,
a.phone,
a.is_active,
a.enterprise_id,
b.identity
FROM
user_info a,
tenant_perms b,
tenant_info c
{where}
{limit}""".format(tenant_id=tenant_id, where=where, limit=limit)
result = conn.query(sql)
return result
def get_app_list(self, tenant_pk, user, tenant_id, region):
user_pk = user.pk
services = []
if user.is_sys_admin:
services = TenantServiceInfo.objects.filter(tenant_id=tenant_id, service_region=region)
else:
perm = perms_repo.get_user_tenant_perm(tenant_pk, user_pk)
if not perm:
if tenant_pk == 5073:
services = TenantServiceInfo.objects.filter(tenant_id=tenant_id, service_region=region).order_by(
'service_alias')
else:
role_name = role_repo.get_role_name_by_role_id(perm.role_id)
if role_name in ('admin', 'developer', 'viewer', 'gray', 'owner'):
services = TenantServiceInfo.objects.filter(tenant_id=tenant_id, service_region=region).order_by(
'service_alias')
else:
dsn = BaseConnection()
add_sql = ''
query_sql = '''
SELECT
s.*
FROM
tenant_service s,
service_perms sp
WHERE
s.tenant_id = "{tenant_id}"
AND sp.user_id = { user_id }
AND sp.service_id = s.ID
AND s.service_region = "{region}" { add_sql }
ORDER BY
s.service_alias'''.format(tenant_id=tenant_id, user_id=user_pk,
region=region, add_sql=add_sql)
services = dsn.query(query_sql)
return services
def list_teams_v2(self, query="", page=None, page_size=None):
where = "WHERE t.creater = u.user_id"
if query:
where += " AND t.tenant_alias LIKE '%{query}%'".format(query=query)
limit = ""
if page is not None and page_size is not None:
page = (page - 1) * page_size
limit = "LIMIT {page}, {page_size}".format(page=page,
page_size=page_size)
sql = """
SELECT
t.tenant_name,
t.tenant_alias,
t.region,
t.limit_memory,
t.enterprise_id,
t.tenant_id,
t.create_time,
t.is_active,
u.nick_name AS creater,
count( s.ID ) AS service_num
FROM
tenant_info t
LEFT JOIN tenant_service s ON t.tenant_id = s.tenant_id,
user_info u
{where}
GROUP BY
tenant_id
ORDER BY
service_num DESC
{limit}
""".format(where=where, limit=limit)
print sql
conn = BaseConnection()
result = conn.query(sql)
return result
def get_by_tenant_id(self, tenant_id, user_id):
conn = BaseConnection()
sql = """
SELECT DISTINCT
a.user_id,
a.email,
a.nick_name,
a.phone,
a.is_active,
a.enterprise_id,
b.identity
FROM
user_info a,
tenant_perms b,
tenant_info c
WHERE a.user_id = b.user_id
AND b.tenant_id = c.ID
AND a.user_id = {user_id}
AND c.tenant_id = '{tenant_id}'""".format(tenant_id=tenant_id, user_id=user_id)
result = conn.query(sql)
if len(result) == 0:
raise UserNotExistError("用户{0}不存在于团队{1}中".format(user_id, tenant_id))
return result[0]
def get_plugins_by_service_id(self, region, tenant_id, service_id,
category):
"""获取组件已开通和未开通的插件"""
QUERY_INSTALLED_SQL = """
SELECT
tp.plugin_id AS plugin_id,
tp.DESC AS "desc",
tp.plugin_alias AS plugin_alias,
tp.category AS category,
tp.origin_share_id AS origin_share_id,
pbv.build_version AS build_version,
pbv.min_memory AS min_memory,
tsp.plugin_status AS plugin_status
FROM
tenant_service_plugin_relation tsp
LEFT JOIN plugin_build_version pbv ON tsp.plugin_id = pbv.plugin_id
AND tsp.build_version = pbv.build_version
JOIN tenant_plugin tp ON tp.plugin_id = tsp.plugin_id
AND tp.tenant_id = pbv.tenant_id
WHERE
tsp.service_id = "{0}"
AND tp.region = "{1}"
AND tp.tenant_id = "{2}" """.format(service_id, region, tenant_id)
QUERI_UNINSTALLED_SQL = """
SELECT
tp.plugin_id AS plugin_id,
tp.DESC AS "desc",
tp.plugin_alias AS plugin_alias,
tp.category AS category,
pbv.build_version AS build_version
FROM
tenant_plugin AS tp
JOIN plugin_build_version AS pbv ON tp.plugin_id = pbv.plugin_id
AND tp.tenant_id = pbv.tenant_id
WHERE
pbv.plugin_id NOT IN ( SELECT plugin_id FROM tenant_service_plugin_relation WHERE service_id = "{0}" )
AND tp.tenant_id = "{1}"
AND tp.region = "{2}"
AND pbv.build_status = "{3}"
""".format(service_id, tenant_id, region, "build_success")
if category == "analysis":
query_installed_plugin = """{0} AND tp.category="{1}" """.format(
QUERY_INSTALLED_SQL, "analyst-plugin:perf")
query_uninstalled_plugin = """{0} AND tp.category="{1}" """.format(
QUERI_UNINSTALLED_SQL, "analyst-plugin:perf")
elif category == "net_manage":
query_installed_plugin = """{0} AND tp.category in {1} """.format(
QUERY_INSTALLED_SQL,
'("net-plugin:down","net-plugin:up","net-plugin:in-and-out")')
query_uninstalled_plugin = """ {0} AND tp.category in {1} """.format(
QUERI_UNINSTALLED_SQL,
'("net-plugin:down","net-plugin:up","net-plugin:in-and-out")')
else:
query_installed_plugin = QUERY_INSTALLED_SQL
query_uninstalled_plugin = QUERI_UNINSTALLED_SQL
dsn = BaseConnection()
installed_plugins = dsn.query(query_installed_plugin)
uninstalled_plugins = dsn.query(query_uninstalled_plugin)
return installed_plugins, uninstalled_plugins