def timingtask(request):
workflowId = request.POST.get('workflowid')
run_date = request.POST.get('run_date')
if run_date is None or workflowId is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
if can_timingtask(request.user, workflowId) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M:%S")
url = getDetailUrl(request) + str(workflowId) + '/'
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflowId)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflowDetail.status = Const.workflowStatus['timingtask']
workflowDetail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflowId, url)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def detail(request, workflow_id):
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in (Const.workflowStatus['finish'], Const.workflowStatus['exception']) \
and workflow_detail.is_manual == 0:
rows = workflow_detail.execute_result
else:
rows = workflow_detail.review_content
list_content = json.loads(rows)
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != Const.workflowStatus['autoreviewwrong']:
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflow_id, 2)
# 是否可审核
is_can_review = Workflow.can_review(request.user, workflow_id, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
# 获取定时执行任务信息
if workflow_detail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = job_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# sql结果
column_list = [
'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL',
'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
context = {
'workflow_detail': workflow_detail,
'column_list': column_list,
'rows': rows,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
def detail(request, workflow_id):
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in ['workflow_finish', 'workflow_exception'] \
and workflow_detail.is_manual == 0:
rows = workflow_detail.execute_result
else:
rows = workflow_detail.review_content
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow_id, 2)
# 是否可审核
is_can_review = Audit.can_review(request.user, workflow_id, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = job_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# 兼容旧数据'[[]]'格式，转换为新格式[{}]
if isinstance(json.loads(rows)[0], list):
review_result = ReviewSet()
for r in json.loads(rows):
review_result.rows += [ReviewResult(inception_result=r)]
rows = review_result.json()
context = {
'workflow_detail': workflow_detail,
'rows': rows,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
def detail(request, workflow_id):
"""展示SQL工单详细页面"""
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if not can_view(request.user, workflow_id):
raise PermissionDenied
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(workflow_id, 2)
# 是否可审核
is_can_review = Audit.can_review(request.user, workflow_id, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
# 是否可查看回滚信息
is_can_rollback = can_rollback(request.user, workflow_id)
# 获取审核日志
try:
audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
last_operation_info = Audit.logs(audit_id=audit_id).latest('id').operation_info
except Exception as e:
logger.debug(f'无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
is_can_rollback = False
last_operation_info = None
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = task_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# 获取是否开启手工执行确认
manual = SysConfig().get('manual')
context = {'workflow_detail': workflow_detail, 'last_operation_info': last_operation_info,
'is_can_review': is_can_review, 'is_can_execute': is_can_execute, 'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel, 'is_can_rollback': is_can_rollback, 'audit_auth_group': audit_auth_group,
'manual': manual, 'current_audit_auth_group': current_audit_auth_group, 'run_date': run_date}
return render(request, 'detail.html', context)
def timing_task(request):
"""
定时执行SQL
:param request:
:return:
"""
# 校验多个权限
if not (request.user.has_perm('sql.sql_execute')
or request.user.has_perm('sql.sql_execute_for_resource_group')):
raise PermissionDenied
workflow_id = request.POST.get('workflow_id')
run_date = request.POST.get('run_date')
if run_date is None or workflow_id is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_timingtask(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
schedule_name = f"sqlreview-timing-{workflow_id}"
if on_correct_time_period(workflow_id, run_date) is False:
context = {'errMsg': '不在可执行时间范围内，如果需要修改执 行时间请重新提交工单!'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = 'workflow_timingtask'
workflow_detail.save()
# 调用添加定时任务
add_sql_schedule(schedule_name, run_date, workflow_id)
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=4,
operation_type_desc='定时执行',
operation_info="定时执行时间：{}".format(run_date),
operator=request.user.username,
operator_display=request.user.display)
except Exception as msg:
logger.error(f"定时执行工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def test_can_timingtask_false(self, ):
"""
测试是否能定时执行的判定条件,当前登录有执行权限,工单状态为审核通过，但用户不是提交人
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_review_pass'
self.wf1.engineer = self.superuser.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_timingtask(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def timingtask(request):
workflow_id = request.POST.get('workflow_id')
run_date = request.POST.get('run_date')
if run_date is None or workflow_id is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_timingtask(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M:%S")
url = get_detail_url(request, workflow_id)
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = Const.workflowStatus['timingtask']
workflow_detail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflow_id, url)
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
workflowOb.add_workflow_log(
audit_id=audit_id,
operation_type=4,
operation_type_desc='定时执行',
operation_info="定时执行时间：{}".format(run_date),
operator=request.user.username,
operator_display=request.user.display)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def timingtask(request):
workflow_id = request.POST.get('workflow_id')
run_date = request.POST.get('run_date')
if run_date is None or workflow_id is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_timingtask(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = 'workflow_timingtask'
workflow_detail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflow_id)
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type[
'sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=4,
operation_type_desc='定时执行',
operation_info="定时执行时间：{}".format(run_date),
operator=request.user.username,
operator_display=request.user.display
)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
def detail(request, workflowId):
workflowDetail = get_object_or_404(SqlWorkflow, pk=workflowId)
if workflowDetail.status in (Const.workflowStatus['finish'], Const.workflowStatus['exception']) \
and workflowDetail.is_manual == 0:
listContent = json.loads(workflowDetail.execute_result)
else:
listContent = json.loads(workflowDetail.review_content)
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflowId, 2)
# 是否可审核
is_can_review = Workflow.can_review(request.user, workflowId, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflowId)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflowId)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflowId)
# 获取定时执行任务信息
if workflowDetail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflowId)
job = job_info(job_id)
if job:
run_date = job.next_run_time
else:
run_date = ''
else:
run_date = ''
# sql结果
column_list = [
'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL',
'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
rows = []
for row_index, row_item in enumerate(listContent):
row = {}
row['ID'] = row_index + 1
row['stage'] = row_item[1]
row['errlevel'] = row_item[2]
row['stagestatus'] = row_item[3]
row['errormessage'] = row_item[4]
row['SQL'] = row_item[5]
row['Affected_rows'] = row_item[6]
row['sequence'] = row_item[7]
row['backup_dbname'] = row_item[8]
row['execute_time'] = row_item[9]
# row['sqlsha1'] = row_item[10]
rows.append(row)
if workflowDetail.status == '执行中':
row['stagestatus'] = ''.join([
"<div id=\"td_" + str(row['ID']) + "\" class=\"form-inline\">",
" <div class=\"progress form-group\" style=\"width: 80%; height: 18px; float: left;\">",
" <div id=\"div_" + str(row['ID']) +
"\" class=\"progress-bar\" role=\"progressbar\"",
" aria-valuenow=\"60\"",
" aria-valuemin=\"0\" aria-valuemax=\"100\">",
" <span id=\"span_" + str(row['ID']) + "\"></span>",
" </div>", " </div>",
" <div class=\"form-group\" style=\"width: 10%; height: 18px; float: right;\">",
" <form method=\"post\">",
" <input type=\"hidden\" name=\"workflowid\" value=\""
+ str(workflowDetail.id) + "\">",
" <button id=\"btnstop_" + str(row['ID']) +
"\" value=\"" + str(row['ID']) + "\"",
" type=\"button\" class=\"close\" style=\"display: none\" title=\"停止pt-OSC进程\">",
" <span class=\"glyphicons glyphicons-stop\">×</span>",
" </button>", " </form>", " </div>", "</div>"
])
context = {
'workflowDetail': workflowDetail,
'column_list': column_list,
'rows': rows,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
def detail(request, workflow_id):
"""展示SQL工单详细页面"""
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in ['workflow_finish', 'workflow_exception']:
rows = workflow_detail.sqlworkflowcontent.execute_result
else:
rows = workflow_detail.sqlworkflowcontent.review_content
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow_id, 2)
# 是否可审核
is_can_review = Audit.can_review(request.user, workflow_id, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
# 获取审核日志
try:
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
last_operation_info = Audit.logs(
audit_id=audit_id).latest('id').operation_info
except Exception as e:
logger.debug(f'无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
last_operation_info = None
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = task_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# 获取是否开启手工执行确认
manual = SysConfig().get('manual')
review_result = ReviewSet()
if rows:
try:
# 检验rows能不能正常解析
loaded_rows = json.loads(rows)
# 兼容旧数据'[[]]'格式，转换为新格式[{}]
if isinstance(loaded_rows[-1], list):
for r in loaded_rows:
review_result.rows += [ReviewResult(inception_result=r)]
rows = review_result.json()
except IndexError:
review_result.rows += [
ReviewResult(
id=1,
sql=workflow_detail.sqlworkflowcontent.sql_content,
errormessage="Json decode failed."
"执行结果Json解析失败, 请联系管理员")
]
rows = review_result.json()
except json.decoder.JSONDecodeError:
review_result.rows += [
ReviewResult(
id=1,
sql=workflow_detail.sqlworkflowcontent.sql_content,
# 迫于无法单元测试这里加上英文报错信息
errormessage="Json decode failed."
"执行结果Json解析失败, 请联系管理员")
]
rows = review_result.json()
else:
rows = workflow_detail.sqlworkflowcontent.review_content
context = {
'workflow_detail': workflow_detail,
'rows': rows,
'last_operation_info': last_operation_info,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'manual': manual,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
