def staticsFee(self):
# data = {}
try:
dsn = BaseConnection()
tenant_sql = '''select tenant_id from tenant_info;'''
tenantIds = dsn.query(tenant_sql)
for tenant_id in tenantIds:
query_lastTime_sql = "select time from tenant_consume where tenant_id='" + tenant_id + "' order by id desc limit 1;"
lastTime = dsn.query(query_lastTime_sql)
curTimeStamp = 0
if len(lastTime) > 0:
logger.debug(lastTime)
last_time = lastTime["time"].strftime('%Y-%m-%d %H:%M:%S')
curTimeStamp = self.dateToTimeStamp(last_time)
query_sql = "select AVG(container_cpu+pod_cpu) as cpu ,AVG(container_memory+pod_memory) as memory,AVG(container_disk+storage_disk) as disk ,AVG(net_in) as net_in,AVG(net_out) as net_out, AVG(node_num) as node_num,service_id from tenant_service_statics where tenant_id='" + tenant_id + "' time_stamp>=" + str(
curTimeStamp) + " group by service_id;"
data = dsn.query(query_sql)
if len(data) > 0:
time = datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S')
for statics in data:
tenantConsumeDetail = TenantConsumeDetail()
tenantConsumeDetail.tenant_id = tenant_id
tenantConsumeDetail.service_id = statics["service_id"]
tenantConsumeDetail.node_num = int(statics["node_num"])
tenantConsumeDetail.cpu = int(statics["cpu"])
tenantConsumeDetail.memory = self.byteToKilo(
int(statics["memory"]))
tenantConsumeDetail.disk = self.byteToKilo(
int(statics["disk"]))
net_in = self.byteToKilo(int(statics["net_in"]))
net_out = self.byteToKilo(int(statics["net_out"]))
if net_in > net_out:
tenantConsumeDetail.net = net_in
else:
tenantConsumeDetail.net = net_out
self.calculateFee(tenantConsumeDetail)
tenantConsumeDetail.time = time
num = TenantRecharge.objects.filter(
tenant_id=tenant_id).count()
if num == 1:
tenantRecharge = TenantRecharge.objects.get(
tenant_id=tenant_id)
tenantRecharge.money = tenantRecharge.money - tenantConsumeDetail.money
tenantRecharge.save()
tenantConsumeDetail.save()
except Exception as e:
logger.exception(e)
def get_no_group_services_list(self, team_id, region_name):
dsn = BaseConnection()
query_sql = '''
SELECT t.service_id,t.service_alias,t.service_cname,t.service_type,t.deploy_version,t.version,t.update_time,t.min_memory*t.min_node as min_memory,g.group_name FROM tenant_service t left join service_group_relation r on t.service_id=r.service_id LEFT join service_group g on r.group_id=g.ID where t.tenant_id="{team_id}" and t.service_region="{region_name}" and r.group_id IS NULL ORDER by t.update_time DESC ;
'''.format(team_id=team_id, region_name=region_name)
services = dsn.query(query_sql)
return services
def get_team_by_group_id(self, group_id):
dsn = BaseConnection()
query_sql = '''
SELECT t.tenant_name FROM service_group_relation s_g_r LEFT JOIN tenant_info t ON s_g_r.tenant_id=t.tenant_id WHERE s_g_r.group_id="{group_id}";
'''.format(group_id=group_id)
team = dsn.query(query_sql)
return team
def get_team_current_region_service_events(self, region, team, page,
page_size):
dsn = BaseConnection()
start = (int(page) - 1) * int(page_size)
end = page_size
query_sql = ''' select e.start_time, e.event_id,s.service_alias,s.service_cname from service_event e JOIN tenant_service s on e.service_id=s.service_id WHERE e.tenant_id="{team_id}" and s.service_region="{region_name}" ORDER BY start_time DESC LIMIT {start},{end} '''.format(
team_id=team.tenant_id, region_name=region, start=start, end=end)
events = dsn.query(query_sql)
events_ids = []
event_id_service_info_map = dict()
for e in events:
events_ids.append(e.event_id)
event_id_service_info_map[e.event_id] = {
"service_alias": e.service_alias,
"service_cname": e.service_cname
}
events = ServiceEvent.objects.filter(
event_id__in=events_ids).order_by("-ID")
try:
self.__sync_region_service_event_status(region, team.tenant_name,
events, False)
except Exception as e:
logger.exception("synchorized services events error !")
for event in events:
bean = event_id_service_info_map.get(event.event_id, None)
if bean:
event.service_alias = bean["service_alias"]
event.service_cname = bean["service_cname"]
return events
def check_custom_rule(self, eid):
"""
check if there is a custom gateway rule
"""
conn = BaseConnection()
sql = """
SELECT
*
FROM
service_domain a,
tenant_info b
WHERE
a.tenant_id = b.tenant_id
AND b.enterprise_id = "{eid}"
AND (
a.certificate_id <> 0
OR ( a.domain_path <> "/" AND a.domain_path <> "" )
OR a.domain_cookie <> ""
OR a.domain_heander <> ""
OR a.the_weight <> 100
OR a.domain_name NOT LIKE concat('%',b.tenant_name,'%')
)
LIMIT 1""".format(eid=eid)
result = conn.query(sql)
return True if len(result) > 0 else False
def get_no_group_services_list(self, team_id, region_name):
dsn = BaseConnection()
query_sql = '''
SELECT
t.service_id,
t.service_alias,
t.service_cname,
t.service_type,
t.create_status,
t.deploy_version,
t.version,
t.update_time,
t.min_memory * t.min_node AS min_memory,
g.group_name
FROM
tenant_service t
LEFT JOIN service_group_relation r ON t.service_id = r.service_id
LEFT JOIN service_group g ON r.group_id = g.ID
WHERE
t.tenant_id = "{team_id}"
AND t.service_region = "{region_name}"
AND r.group_id IS NULL
ORDER BY
t.update_time DESC;
'''.format(team_id=team_id, region_name=region_name)
services = dsn.query(query_sql)
return services
def get_event_service_dynamic(self, team_id, region_name):
dsn = BaseConnection()
query_sql = '''
SELECT e.type,e.start_time,e.status,e.final_status,s.service_alias,s.service_cname,u.nick_name,u.user_id FROM `service_event` e,tenant_service s, user_info u WHERE e.service_id=s.service_id and e.user_name=u.nick_name and e.tenant_id="{team_id}" and s.service_region="{region_name}" ORDER BY start_time DESC LIMIT 0,60;
'''.format(team_id=team_id, region_name=region_name)
event_service_dynamic = dsn.query(query_sql)
return event_service_dynamic
def check_db_dep_by_eid(self, eid):
"""
check if there is a database installed from the market that is dependent on
"""
conn = BaseConnection()
sql = """
SELECT
a.service_id, a.dep_service_id
FROM
tenant_service_relation a,
tenant_service b,
tenant_info c,
tenant_service d
WHERE
b.tenant_id = c.tenant_id
AND c.enterprise_id = "{eid}"
AND a.service_id = d.service_id
AND a.dep_service_id = b.service_id
AND ( b.image LIKE "%mysql%" OR b.image LIKE "%postgres%" OR b.image LIKE "%mariadb%" )
AND (b.service_source <> "market" OR d.service_source <> "market")
limit 1""".format(eid=eid)
result = conn.query(sql)
if len(result) > 0:
return True
sql2 = """
SELECT
a.dep_service_id
FROM
tenant_service_relation a,
tenant_service b,
tenant_info c,
tenant_service d,
service_source e,
service_source f
WHERE
b.tenant_id = c.tenant_id
AND c.enterprise_id = "{eid}"
AND a.service_id = d.service_id
AND a.dep_service_id = b.service_id
AND ( b.image LIKE "%mysql%" OR b.image LIKE "%postgres%" OR b.image LIKE "%mariadb%" )
AND ( b.service_source = "market" AND d.service_source = "market" )
AND e.service_id = b.service_id
AND f.service_id = d.service_id
AND e.group_key <> f.group_key
LIMIT 1""".format(eid=eid)
result2 = conn.query(sql2)
return True if len(result2) > 0 else False
def post(self, request, format=None):
"""
获取所有租户信息
---
parameters:
- name: service_status
description: 服务状态
required: true
type: string
paramType: form
- name: pay_type
description: 租户类型
required: true
type: string
paramType: form
- name: region
description: 区域中心
required: true
type: string
paramType: form
- name: day
description: 相差天数
required: false
type: string
paramType: form
"""
service_status = request.data.get('service_status', "1")
pay_type = request.data.get('pay_type', "free")
region = request.data.get('region', "")
query_day = request.data.get('day', "0")
diff_day = int(query_day)
data = {}
try:
if region != "":
dsn = BaseConnection()
query_sql = ""
if diff_day != 0:
end_time = datetime.datetime.now() + datetime.timedelta(
days=-1 * diff_day)
str_time = end_time.strftime("%Y-%m-%d %H:%M:%S")
query_sql = '''select ti.tenant_id,ti.tenant_name from tenant_info ti left join tenant_region tr on ti.tenant_id=tr.tenant_id where tr.is_init=1 and tr.service_status="{service_status}" and ti.pay_type="{pay_type}" and tr.region_name="{region}" and tr.update_time <= "{end_time}"
'''.format(service_status=service_status,
pay_type=pay_type,
region=region,
end_time=str_time)
else:
query_sql = '''select ti.tenant_id,ti.tenant_name from tenant_info ti left join tenant_region tr on ti.tenant_id=tr.tenant_id where tr.is_init=1 and tr.service_status="{service_status}" and ti.pay_type="{pay_type}" and tr.region_name="{region}"
'''.format(service_status=service_status,
pay_type=pay_type,
region=region)
if query_sql != "":
sqlobjs = dsn.query(query_sql)
if sqlobjs is not None and len(sqlobjs) > 0:
for sqlobj in sqlobjs:
data[sqlobj['tenant_id']] = sqlobj['tenant_name']
except Exception as e:
logger.exception(e)
return Response(data, status=200)
def get(self, request, *args, **kwargs):
data = {}
try:
action = request.GET.get("action", "")
if action == "gitlab":
tenant_id = self.tenant.tenant_id
dsn = BaseConnection()
query_sql = '''
select distinct git_url, git_project_id from tenant_service s where s.tenant_id = "{tenant_id}" and code_from="gitlab_new" and git_project_id>0
'''.format(tenant_id=tenant_id)
sqlobjList = dsn.query(query_sql)
if sqlobjList is not None:
arr = []
for sqlobj in sqlobjList:
d = {}
d["code_repos"] = sqlobj.git_url
d["code_user"] = sqlobj.git_url.split(":")[1].split(
"/")[0]
d["code_project_name"] = sqlobj.git_url.split(
":")[1].split("/")[1].split(".")[0]
d["code_id"] = sqlobj.git_project_id
arr.append(d)
data["data"] = arr
data["status"] = "success"
elif action == "github":
token = self.user.github_token
if token is not None:
repos = codeRepositoriesService.getgGitHubAllRepos(token)
reposList = json.loads(repos)
if isinstance(reposList, dict):
data["status"] = "unauthorized"
data[
"url"] = codeRepositoriesService.gitHub_authorize_url(
self.user)
else:
arr = []
for reposJson in reposList:
d = {}
clone_url = reposJson["clone_url"]
code_id = reposJson["id"]
d["code_id"] = code_id
d["code_repos"] = clone_url
d["code_user"] = clone_url.split("/")[3]
d["code_project_name"] = clone_url.split(
"/")[4].split(".")[0]
arr.append(d)
data["data"] = arr
data["status"] = "success"
else:
data["status"] = "unauthorized"
data["url"] = codeRepositoriesService.gitHub_authorize_url(
self.user)
except Exception as e:
logger.exception(e)
return JsonResponse(data, status=200)
def get_plugins_by_service_ids(self, service_ids):
if not service_ids:
return []
ids = ""
for sid in service_ids:
ids += "\"{0}\",".format(sid)
if len(ids) > 1:
ids = ids[:-1]
dsn = BaseConnection()
query_sql = '''
select t.*,p.build_version from tenant_plugin t,plugin_build_version p,tenant_service_plugin_relation r where r.service_id in({service_ids}) and t.plugin_id=r.plugin_id and p.build_version=r.build_version
'''.format(service_ids=ids)
plugins = dsn.query(query_sql)
return plugins
def update_using(self, service_key):
app = App.objects.only('ID').get(service_key=service_key)
dsn = BaseConnection()
sql = "select count(1) as Count, creater as user_id from tenant_service where service_key='{}' group by creater".format(
service_key)
result = dsn.query(sql)
for i in result:
using, created = AppUsing.objects.get_or_create(app_id=app.pk,
user_id=i.user_id)
using.install_count = i.Count
using.save(update_fields=['install_count'])
app.using = AppUsing.objects.filter(app_id=app.pk).count()
app.save()
def get_fuzzy_services_list(self, team_id, region_name, query_key, fields,
order):
if fields != "update_time" and fields != "ID":
fields = "ID"
if order != "desc" and order != "asc":
order = "desc"
dsn = BaseConnection()
query_sql = '''
SELECT t.create_status, t.service_id,t.service_cname,t.min_memory*t.min_node as min_memory,t.service_alias,t.service_type,t.deploy_version,t.version,t.update_time,r.group_id,g.group_name FROM tenant_service t left join service_group_relation r on t.service_id=r.service_id LEFT join service_group g on r.group_id=g.ID where t.tenant_id="{team_id}" and t.service_region="{region_name}" and t.service_cname LIKE "%{query_key}%" ORDER by t.{fields} {order} ;
'''.format(team_id=team_id,
region_name=region_name,
query_key=query_key,
fields=fields,
order=order)
services = dsn.query(query_sql)
return services
def check_app_by_eid(self, eid):
"""
check if an app has been shared
"""
conn = BaseConnection()
sql = """
SELECT
a.team_name
FROM
service_share_record a,
tenant_info b
WHERE
a.team_name = b.tenant_name
AND b.enterprise_id = "{eid}"
LIMIT 1""".format(eid=eid)
result = conn.query(sql)
return True if len(result) > 0 else False
def check_non_default_group_by_eid(self, eid):
conn = BaseConnection()
sql = """
SELECT
group_name
FROM
service_group a,
tenant_info b
WHERE
a.tenant_id = b.tenant_id
AND a.region_name = b.region
AND a.is_default = FALSE
AND b.enterprise_id = "{eid}"
LIMIT 1;
""".format(eid=eid)
result = conn.query(sql)
return True if len(result) > 0 else False
def check_plugins_by_eid(self, eid):
"""
check if an app has been shared
"""
conn = BaseConnection()
sql = """
SELECT
a.plugin_id
FROM
tenant_service_plugin_relation a,
tenant_service c,
tenant_info b
WHERE
c.tenant_id = b.tenant_id
AND a.service_id = c.service_id
AND c.service_source <> "market"
AND b.enterprise_id = "{eid}"
LIMIT 1""".format(eid=eid)
result = conn.query(sql)
return True if len(result) > 0 else False