def execute(self):
'''
:argument 将获得的sql语句提交给inception执行并将返回结果写入SqlRecord表,最后更改该工单SqlOrder表中的status
:param
self.order
self.id
:return: none
'''
time.sleep(self.order.delay * 60)
try:
detail = DatabaseList.objects.filter(id=self.order.bundle_id).first()
with call_inception.Inception(
LoginDic={
'host': detail.ip,
'user': detail.username,
'password': detail.password,
'db': self.order.basename,
'port': detail.port
}
) as f:
res = f.Execute(sql=self.order.sql, backup=self.order.backup)
for i in res:
#if i['errlevel'] != 0:
if i['errlevel'] == 1:
SqlOrder.objects.filter(work_id=self.order.work_id).update(status=5)
if i['errlevel'] == 2:
SqlOrder.objects.filter(work_id=self.order.work_id).update(status=4)
SqlRecord.objects.get_or_create(
state=i['stagestatus'],
sql=i['sql'],
error=i['errormessage'],
workid=self.order.work_id,
affectrow=i['affected_rows'],
sequence=i['sequence'],
execute_time=i['execute_time'],
SQLSHA1=i['SQLSHA1'],
backup_dbname=i['backup_dbname']
)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}--邮箱推送失败: {e}')
finally:
status = SqlOrder.objects.filter(work_id=self.order.work_id).first()
if status.status != 4:
SqlOrder.objects.filter(id=self.id).update(status=1)
def execute(self):
self.title = f'工单:{self.order.work_id}审核通过通知'
'''
根据工单编号拿出对应sql的拆解数据
'''
SQL_LIST = DatabaseList.objects.filter(id=self.order.bundle_id).first()
'''
发送sql语句到inception中执行
'''
with call_inception.Inception(
LoginDic={
'host': SQL_LIST.ip,
'user': SQL_LIST.username,
'password': SQL_LIST.password,
'db': self.order.basename,
'port': SQL_LIST.port
}) as f:
res = f.Execute(sql=self.order.sql, backup=self.order.backup)
'''
修改该工单编号的state状态
'''
SqlOrder.objects.filter(id=self.id).update(status=1)
'''
遍历返回结果插入到执行记录表中
'''
for i in res:
SqlRecord.objects.get_or_create(
date=util.date(),
state=i['stagestatus'],
sql=i['sql'],
area=SQL_LIST.computer_room,
name=SQL_LIST.connection_name,
error=i['errormessage'],
base=self.order.basename,
workid=self.order.work_id,
person=self.order.username,
reviewer=self.order.assigned,
affectrow=i['affected_rows'],
sequence=i['sequence'],
backup_dbname=i['backup_dbname'])
def execute(self):
'''
:argument 将获得的sql语句提交给inception执行并将返回结果写入SqlRecord表,最后更改该工单SqlOrder表中的status
:param
self.order
self.id
:return: none
'''
detail = DatabaseList.objects.filter(id=self.order.bundle_id).first()
with call_inception.Inception(
LoginDic={
'host': detail.ip,
'user': detail.username,
'password': detail.password,
'db': self.order.basename,
'port': detail.port
}
) as f:
res = f.Execute(sql=self.order.sql, backup=self.order.backup)
SqlOrder.objects.filter(id=self.id).update(status=1)
for i in res:
if i['stagestatus'].find('failed') != -1:
self.statue = 'bad'
SqlRecord.objects.get_or_create(
date=util.date(),
state=i['stagestatus'],
sql=i['sql'],
area=detail.computer_room,
name=detail.connection_name,
error=i['errormessage'],
base=self.order.basename,
workid=self.order.work_id,
person=self.order.username,
reviewer=self.order.assigned,
affectrow=i['affected_rows'],
sequence=i['sequence'],
backup_dbname=i['backup_dbname'],
execute_time=i['execute_time'],
SQLSHA1=i['SQLSHA1']
)
def put(self, request, args=None):
if args == 'beautify':
try:
data = request.data['data']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
res = call_inception.Inception.BeautifySQL(sql=data)
return HttpResponse(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'test':
print("test 测试")
try:
id = request.data['id']
base = request.data['base']
sql = request.data['sql']
print("断点")
sql = str(sql).strip('\n').strip().rstrip(';')
data = DatabaseList.objects.filter(id=id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base,
'port': data.port
}
print(info)
except KeyError as e:
print("异常")
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
with call_inception.Inception(LoginDic=info) as test:
print("sql"+sql)
res = test.Check(sql=sql)
return Response({'result': res, 'status': 200})
except Exception as e:
traceback.print_exc()
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
def get(self, request, args: str = None):
'''
:argument 根据获得的sha1,返回对应sql的osc进度
'''
try:
with call_inception.Inception(LoginDic={
'host': '',
'user': '',
'password': '',
'db': '',
'port': ''
}) as f:
data = f.oscstep(sql="inception get osc_percent '%s';" % args)
return Response(data)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
def delete(self, request, args: str = None):
'''
:argument: 根据获得的SHA1, 终止对应sql的osc 并返回执行结果
'''
try:
with call_inception.Inception(LoginDic={
'host': '',
'user': '',
'password': '',
'db': '',
'port': ''
}) as f:
f.oscstep(sql=f"inception stop alter '{args}';")
return Response('osc已终止,请刷新后查看详细信息')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
def put(self, request, args=None):
if args == 'beautify':
try:
data = request.data['data']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
res = call_inception.Inception.BeautifySQL(sql=data)
return HttpResponse(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'test':
try:
connect_name = request.data['connect_name']
sql = request.data['sql']
sql = str(sql).strip('\n').strip().rstrip(';')
data = DatabaseList.objects.filter(
connection_name=connect_name).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': data.dbname,
'port': data.port
}
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response(e)
def put(self, request, args=None):
try:
type = request.data['type']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if type == 0:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
text = request.data['text']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=id).update(status=0)
_tmpData = SqlOrder.objects.filter(id=id).values(
'work_id',
'bundle_id'
).first()
title = '工单:' + _tmpData['work_id'] + '驳回通知'
Usermessage.objects.get_or_create(
from_user=from_user,
time=util.date(),
title=title,
content=text,
to_user=to_user,
state='unread'
)
rejected_push_messages(_tmpData, to_user, addr_ip, text).start()
return Response('操作成功，该请求已驳回！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif type == 1:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=id).update(status=3)
order_push_message(addr_ip, id, from_user, to_user).start()
return Response('工单执行成功!请通过记录页面查看具体执行结果')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif type == 'test':
try:
base = request.data['base']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
sql = SqlOrder.objects.filter(id=id).first()
data = DatabaseList.objects.filter(id=sql.bundle_id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base,
'port': data.port
}
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql.sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'status': '500'})
def put(self, request, args: str = None):
'''
:argument 工单确认执行,驳回,二次检测接口。
:param category 根据获得的category值执行具体的操作逻辑
:return 提交结果信息
'''
try:
category = request.data['type']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if category == 0:
try:
to_user = request.data['to_user']
text = request.data['text']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=order_id).update(
status=0, rejected=text)
_tmpData = SqlOrder.objects.filter(id=order_id).values(
'work_id', 'bundle_id').first()
reject = rejected_push_messages(
_tmpData, to_user, addr_ip, text, request.user)
threading.Timer(0, reject.execute).start()
return Response('操作成功，该请求已驳回！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif category == 1:
try:
from_user = request.user
to_user = request.data['to_user']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
idempotent = SqlOrder.objects.filter(
id=order_id).first()
if idempotent.status != 2:
return Response('非法传参，触发幂等操作')
else:
delay = 0
if str(idempotent.delay).rstrip() != '':
now_time = datetime.datetime.now()
next_time = datetime.datetime.strptime(
idempotent.delay, "%Y-%m-%d %H:%M")
if now_time > next_time:
return Response('工单定时执行时间不得小于当前时间！！！')
delay = int(
(next_time - now_time).total_seconds())
SqlOrder.objects.filter(id=order_id).update(
status=3)
arr = order_push_message(addr_ip, order_id,
from_user, to_user)
threading.Timer(delay, arr.run).start()
return Response('工单执行成功!请通过记录页面查看具体执行结果')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif category == 2:
try:
perform = request.data['perform']
work_id = request.data['work_id']
username = request.data['username']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
mail = Account.objects.filter(username=perform).first()
SqlOrder.objects.filter(work_id=work_id).update(
executor=perform)
threading.Thread(target=push_message,
args=({
'to_user': username,
'workid': work_id,
'addr': addr_ip
}, 9, request.user, mail.email, work_id,
'已提交执行人')).start()
return Response('工单已提交执行人！')
elif category == 'test':
try:
base = request.data['base']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
sql = SqlOrder.objects.filter(id=order_id).first()
if not sql.sql:
return Response({'status': '工单内无sql语句!'})
data = DatabaseList.objects.filter(
id=sql.bundle_id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base,
'port': data.port
}
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql.sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'status': '请检查inception信息是否正确!'})
def execute(self):
'''
:argument 将获得的sql语句提交给inception执行并将返回结果写入SqlRecord表,最后更改该工单SqlOrder表中的status
:param
self.order
self.id
:return: none
'''
if self.order.type != 2:
try:
detail = DatabaseList.objects.filter(
id=self.order.bundle_id).first()
with call_inception.Inception(
LoginDic={
'host': detail.ip,
'user': detail.username,
'password': detail.password,
'db': self.order.basename,
'port': detail.port
}) as f:
res = f.Execute(sql=self.order.sql,
backup=self.order.backup)
for i in res:
if i['errlevel'] != 0:
SqlOrder.objects.filter(
work_id=self.order.work_id).update(status=4)
SqlRecord.objects.get_or_create(
state=i['stagestatus'],
sql=i['sql'],
error=i['errormessage'],
workid=self.order.work_id,
affectrow=i['affected_rows'],
sequence=i['sequence'],
execute_time=i['execute_time'],
SQLSHA1=i['SQLSHA1'],
backup_dbname=i['backup_dbname'])
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
finally:
status = SqlOrder.objects.filter(
work_id=self.order.work_id).first()
if status.status != 4:
SqlOrder.objects.filter(id=self.id).update(status=1)
else:
# dql 操作
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
sql = self.order.sql
check = str(self.order.sql).lower().strip().split(';\n')
raw_sql = str(self.order.sql).strip().split(';\n')[-1]
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
critical = len(custom_com['sensitive_list'])
# 操作实例
detail = DatabaseList.objects.filter(
id=self.order.bundle_id).first()
with con_database.SQLgo(ip=detail.ip,
password=detail.password,
user=detail.username,
port=detail.port,
db=self.order.basename) as f:
try:
if check[-1].startswith('show'):
query_sql = raw_sql
else:
if limit.get('limit').strip() == '':
CUSTOM_ERROR.error('未设置全局最大limit值，系统自动设置为1000')
query_sql = replace_limit(raw_sql, 1000)
else:
query_sql = replace_limit(raw_sql,
limit.get('limit'))
data_set = f.search(sql=query_sql)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if self.order.sensitive:
as_list = sql_as_ex(sql, custom_com['sensitive_list'])
if data_set['data']:
fe = []
for k, v in data_set['data'][0].items():
if isinstance(v, bytes):
fe.append(k)
for l in data_set['data']:
if len(fe) != 0:
for i in fe:
l[i] = 'blob字段为不可呈现类型'
for s in as_list:
if l.get(s):
if s == "email":
pattern = re.compile(r"(.*)@(.*)")
res = re.findall(pattern, l[s])
if len(res) != 0:
l[s] = res[0][0] + "*****"
elif s == "phone":
pattern = re.compile(
r"(.{3})(.*)(.{4})")
res = re.findall(pattern, l[s])
if len(res) != 0:
l[s] = res[0][
0] + "*****" + res[0][-1]
elif s == "idno":
pattern = re.compile(
r"(.*)(.{4})$")
res = re.findall(pattern, l[s])
if len(res) != 0:
l[s] = res[0][0] + "*****"
else:
l[s] = l[s][:3] + "****" + l[s][-3:]
else:
continue
else:
if data_set['data']:
fe = []
for k, v in data_set['data'][0].items():
if isinstance(v, bytes):
fe.append(k)
if len(fe) != 0:
for l in data_set['data']:
for i in fe:
l[i] = 'blob字段为不可呈现类型'
if len(data_set["data"]) > 200:
from settingConf.settings import MEDIA_ROOT
filename, status = file_help.save_file(
data_set, MEDIA_ROOT)
if status:
querypermissions.objects.create(
work_id=self.order.work_id,
username=self.order.username,
statements=query_sql,
filename=filename,
answer={
"data": [],
"title": [],
"len": ""
})
else:
CUSTOM_ERROR.error("***file save fail***",
filename)
else:
querypermissions.objects.create(
work_id=self.order.work_id,
username=self.order.username,
statements=query_sql,
answer=json.dumps(data_set))
SqlOrder.objects.filter(work_id=self.order.work_id).update(
status=1)
def put(self, request, args=None):
try:
type = request.data['type']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if type == 0:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
text = request.data['text']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=id).update(status=0)
_tmpData = SqlOrder.objects.filter(id=id).values(
'work_id', 'bundle_id').first()
title = '工单:' + _tmpData['work_id'] + '驳回通知'
Usermessage.objects.get_or_create(from_user=from_user,
time=util.date(),
title=title,
content=text,
to_user=to_user,
state='unread')
content = DatabaseList.objects.filter(
id=_tmpData['bundle_id']).first()
if content.url:
util.dingding(content='工单驳回通知\n' + text,
url=content.url)
return Response('操作成功，该请求已驳回！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif type == 1:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
c = SqlOrder.objects.filter(id=id).first()
title = f'工单:{c.work_id}审核通过通知'
'''
根据工单编号拿出对应sql的拆解数据
'''
SQL_LIST = DatabaseList.objects.filter(
id=c.bundle_id).first()
'''
发送sql语句到inception中执行
'''
with call_inception.Inception(
LoginDic={
'host': SQL_LIST.ip,
'user': SQL_LIST.username,
'password': SQL_LIST.password,
'db': c.basename,
'port': SQL_LIST.port
}) as f:
res = f.Execute(sql=c.sql, backup=c.backup)
'''
遍历返回结果插入到执行记录表中
'''
for i in res:
SqlRecord.objects.get_or_create(
date=util.date(),
state=i['stagestatus'],
sql=i['sql'],
area=SQL_LIST.computer_room,
name=SQL_LIST.connection_name,
error=i['errormessage'],
base=c.basename,
workid=c.work_id,
person=c.username,
reviewer=from_user,
affectrow=i['affected_rows'],
sequence=i['sequence'],
backup_dbname=i['backup_dbname'])
if c.type == 0 and \
i['errlevel'] == 0 and \
i['sql'].find('use') == -1 and \
i['stagestatus'] != 'Audit completed':
data = testddl.AutomaticallyDDL(
sql=" ".join(i['sql'].split()))
if data['mode'] == 'edit':
SqlDictionary.objects.filter(
BaseName=data['BaseName'],
TableName=data['TableName'],
Field=data['Field']).update(
Type=data['Type'],
Null=data['Null'],
Default=data['Default'])
elif data['mode'] == 'add':
SqlDictionary.objects.get_or_create(
Type=data['Type'],
Null=data['Null'],
Default=data['Default'],
Extra=data['COMMENT'],
BaseName=data['BaseName'],
TableName=data['TableName'],
Field=data['Field'],
TableComment='',
Name=SQL_LIST.connection_name)
elif data['mode'] == 'del':
SqlDictionary.objects.filter(
BaseName=data['BaseName'],
TableName=data['TableName'],
Field=data['Field'],
Name=SQL_LIST.connection_name
).delete()
'''
修改该工单编号的state状态
'''
SqlOrder.objects.filter(id=id).update(status=1)
'''
通知消息
'''
Usermessage.objects.get_or_create(from_user=from_user,
time=util.date(),
title=title,
content='该工单已审核通过!',
to_user=to_user,
state='unread')
'''
Dingding
'''
content = DatabaseList.objects.filter(
id=c.bundle_id).first()
if content.url:
util.dingding(content='工单执行通知\n' + content.after,
url=content.url)
return Response(
'操作成功，该请求已同意!并且已在相应库执行！详细执行信息请前往执行记录页面查看！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif type == 'test':
try:
base = request.data['base']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
sql = SqlOrder.objects.filter(id=id).first()
data = DatabaseList.objects.filter(
id=sql.bundle_id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base
}
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql.sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'status': '500'})
def put(self, request, args: str = None):
'''
:argument 工单确认执行,驳回,二次检测接口。
:param category 根据获得的category值执行具体的操作逻辑
:return 提交结果信息
'''
try:
category = request.data['type']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if category == 0:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
text = request.data['text']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=order_id).update(status=0)
_tmpData = SqlOrder.objects.filter(id=order_id).values(
'work_id', 'bundle_id').first()
title = '工单:' + _tmpData['work_id'] + '驳回通知'
Usermessage.objects.get_or_create(from_user=from_user,
time=util.date(),
title=title,
content=text,
to_user=to_user,
state='unread')
rejected_push_messages(_tmpData, to_user, addr_ip,
text).start()
return Response('操作成功，该请求已驳回！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif category == 1:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=order_id).update(status=3)
order_push_message(addr_ip, order_id, from_user,
to_user).start()
return Response('工单执行成功!请通过记录页面查看具体执行结果')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif category == 2:
try:
perform = request.data['perform']
work_id = request.data['work_id']
username = request.data['username']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
mail = Account.objects.filter(username=username).first()
SqlOrder.objects.filter(work_id=work_id).update(
assigned=perform)
threading.Thread(target=push_message,
args=({
'to_user': request.user,
'workid': work_id
}, 2, request.user, mail.email, work_id,
'已同意')).start()
return Response('工单已提交执行人！')
elif category == 'test':
try:
base = request.data['base']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
sql = SqlOrder.objects.filter(id=order_id).first()
if not sql.sql:
return Response({'status': '工单内无sql语句!'})
data = DatabaseList.objects.filter(
id=sql.bundle_id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base,
'port': data.port
}
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql.sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'status': '请检查inception信息是否正确!'})
def execute(self):
self.title = f'工单:{self.order.work_id}审核通过通知'
'''
根据工单编号拿出对应sql的拆解数据
'''
SQL_LIST = DatabaseList.objects.filter(id=self.order.bundle_id).first()
'''
发送sql语句到inception中执行
'''
with call_inception.Inception(
LoginDic={
'host': SQL_LIST.ip,
'user': SQL_LIST.username,
'password': SQL_LIST.password,
'db': self.order.basename,
'port': SQL_LIST.port
}) as f:
res = f.Execute(sql=self.order.sql, backup=self.order.backup)
'''
修改该工单编号的state状态
'''
SqlOrder.objects.filter(id=self.id).update(status=1)
'''
遍历返回结果插入到执行记录表中
'''
for i in res:
SqlRecord.objects.get_or_create(
date=util.date(),
state=i['stagestatus'],
sql=i['sql'],
area=SQL_LIST.computer_room,
name=SQL_LIST.connection_name,
error=i['errormessage'],
base=self.order.basename,
workid=self.order.work_id,
person=self.order.username,
reviewer=self.order.assigned,
affectrow=i['affected_rows'],
sequence=i['sequence'],
backup_dbname=i['backup_dbname'])
if self.order.type == 0 and \
i['errlevel'] == 0 and \
i['sql'].find('use') == -1 and \
i['stagestatus'] != 'Audit completed':
data = testddl.AutomaticallyDDL(
sql=" ".join(i['sql'].split()))
if data['mode'] == 'pass':
pass
elif data['mode'] == 'add':
SqlDictionary.objects.get_or_create(
Type=data['Type'],
Null=data['Null'],
Default=data['Default'],
Extra=data['COMMENT'],
BaseName=data['BaseName'],
TableName=data['TableName'],
Field=data['Field'],
TableComment='',
Name=SQL_LIST.connection_name)
elif data['mode'] == 'del':
SqlDictionary.objects.filter(
BaseName=data['BaseName'],
TableName=data['TableName'],
Field=data['Field'],
Name=SQL_LIST.connection_name).delete()
elif data['mode'] == 'drop':
SqlDictionary.objects.filter(
BaseName=self.order.basename,
TableName=data['TableName']).delete()
def put(self, request, args=None):
try:
type = request.data['type']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if type == 0:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
text = request.data['text']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=id).update(status=0)
_tmpData = SqlOrder.objects.filter(id=id).values(
'work_id', 'bundle_id').first()
title = '工单:' + _tmpData['work_id'] + '驳回通知'
Usermessage.objects.get_or_create(from_user=from_user,
time=util.date(),
title=title,
content=text,
to_user=to_user,
state='unread')
content = DatabaseList.objects.filter(
id=_tmpData['bundle_id']).first()
mail = Account.objects.filter(username=to_user).first()
tag = globalpermissions.objects.filter(
authorization='global').first()
ret_info = '操作成功，该请求已驳回！'
if tag is None or tag.dingding == 0:
pass
else:
try:
if content.url:
util.dingding(
content=
'工单驳回通知\n工单编号:%s\n发起人:%s\n地址:%s\n驳回说明:%s\n状态:驳回'
% (_tmpData['work_id'], to_user,
addr_ip, text),
url=content.url)
except:
ret_info = '工单执行成功!但是钉钉推送失败,请查看错误日志排查错误.'
if tag is None or tag.email == 0:
pass
else:
try:
if mail.email:
mess_info = {
'workid': _tmpData['work_id'],
'to_user': to_user,
'addr': addr_ip,
'rejected': text
}
put_mess = send_email.send_email(
to_addr=mail.email)
put_mess.send_mail(mail_data=mess_info,
type=1)
except:
ret_info = '工单执行成功!但是邮箱推送失败,请查看错误日志排查错误.'
return Response(ret_info)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif type == 1:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=id).update(status=3)
c = SqlOrder.objects.filter(id=id).first()
title = f'工单:{c.work_id}审核通过通知'
'''
根据工单编号拿出对应sql的拆解数据
'''
SQL_LIST = DatabaseList.objects.filter(
id=c.bundle_id).first()
'''
发送sql语句到inception中执行
'''
with call_inception.Inception(
LoginDic={
'host': SQL_LIST.ip,
'user': SQL_LIST.username,
'password': SQL_LIST.password,
'db': c.basename,
'port': SQL_LIST.port
}) as f:
res = f.Execute(sql=c.sql, backup=c.backup)
'''
修改该工单编号的state状态
'''
SqlOrder.objects.filter(id=id).update(status=1)
'''
遍历返回结果插入到执行记录表中
'''
for i in res:
SqlRecord.objects.get_or_create(
date=util.date(),
state=i['stagestatus'],
sql=i['sql'],
area=SQL_LIST.computer_room,
name=SQL_LIST.connection_name,
error=i['errormessage'],
base=c.basename,
workid=c.work_id,
person=c.username,
reviewer=c.assigned,
affectrow=i['affected_rows'],
sequence=i['sequence'],
backup_dbname=i['backup_dbname'])
'''
通知消息
'''
Usermessage.objects.get_or_create(from_user=from_user,
time=util.date(),
title=title,
content='该工单已审核通过!',
to_user=to_user,
state='unread')
'''
Dingding
'''
content = DatabaseList.objects.filter(
id=c.bundle_id).first()
mail = Account.objects.filter(username=to_user).first()
tag = globalpermissions.objects.filter(
authorization='global').first()
ret_info = '操作成功，该请求已同意!并且已在相应库执行！详细执行信息请前往执行记录页面查看！'
if tag is None or tag.dingding == 0:
pass
else:
try:
if content.url:
util.dingding(
content=
'工单执行通知\n工单编号:%s\n发起人:%s\n地址:%s\n工单备注:%s\n状态:同意\n备注:%s'
% (c.work_id, c.username, addr_ip,
c.text, content.after),
url=content.url)
except:
ret_info = '工单执行成功!但是钉钉推送失败,请查看错误日志排查错误.'
if tag is None or tag.email == 0:
pass
else:
try:
if mail.email:
mess_info = {
'workid': c.work_id,
'to_user': c.username,
'addr': addr_ip,
'text': c.text,
'note': content.after
}
put_mess = send_email.send_email(
to_addr=mail.email)
put_mess.send_mail(mail_data=mess_info,
type=0)
except:
ret_info = '工单执行成功!但是邮箱推送失败,请查看错误日志排查错误.'
return Response(ret_info)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif type == 'test':
try:
base = request.data['base']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
sql = SqlOrder.objects.filter(id=id).first()
data = DatabaseList.objects.filter(
id=sql.bundle_id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base,
'port': data.port
}
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql.sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'status': '500'})
def put(self, request, args: str=None):
'''
:argument 工单确认执行,驳回,二次检测接口。
:param category 根据获得的category值执行具体的操作逻辑
:return 提交结果信息
'''
try:
category = request.data['type']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
DJANGO_INFO.info(request.data)
if category == 0:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
text = request.data['text']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
SqlOrder.objects.filter(id=order_id).update(status=0)
_tmpData = SqlOrder.objects.filter(id=order_id).values(
'work_id',
'bundle_id'
).first()
title = '工单:' + _tmpData['work_id'] + '驳回通知'
Usermessage.objects.get_or_create(
from_user=from_user,
time=util.date(),
title=title,
content=text,
to_user=to_user,
state='unread'
)
rejected_push_messages(_tmpData, to_user, addr_ip, text).start()
return Response('操作成功，该请求已驳回！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif category == 1:
try:
from_user = request.data['from_user']
to_user = request.data['to_user']
order_id = request.data['id']
audit_id = request.data['audit']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
DJANGO_INFO.info(f'{addr_ip}, {order_id}, {from_user}, {to_user}')
'''audit'''
if audit_id == 1:
SqlOrder.objects.filter(id=order_id).filter(username=from_user).update(status=3)
order_push_message(addr_ip, order_id, from_user, to_user).start()
return Response('工单执行成功!请通过记录页面查看具体执行结果')
else:
SqlOrder.objects.filter(id=order_id).filter(assigned=from_user).update(status=3)
return Response('工单审批成功!请通过记录页面查看具体审批结果')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif category == 'test':
try:
base = request.data['base']
order_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
sql = SqlOrder.objects.filter(id=order_id).first()
data = DatabaseList.objects.filter(id=sql.bundle_id).first()
info = {
'host': data.ip,
'user': data.username,
'password': data.password,
'db': base,
'port': data.port
}
try:
with call_inception.Inception(LoginDic=info) as test:
res = test.Check(sql=sql.sql)
return Response({'result': res, 'status': 200})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'status': '500'})
