def can_review(user, workflow_id, workflow_type):
audit_info = WorkflowAudit.objects.get(workflow_id=workflow_id,
workflow_type=workflow_type)
group_id = audit_info.group_id
result = False
# 只有待审核状态数据才可以审核
if audit_info.current_status == WorkflowDict.workflow_status[
'audit_wait']:
try:
auth_group_id = Audit.detail_by_workflow_id(
workflow_id, workflow_type).current_audit
audit_auth_group = Group.objects.get(id=auth_group_id).name
except Exception:
raise Exception('当前审批auth_group_id不存在，请检查并清洗历史数据')
if auth_group_users([audit_auth_group], group_id).filter(
id=user.id).exists() or user.is_superuser == 1:
if workflow_type == 1:
if user.has_perm('sql.query_review'):
result = True
elif workflow_type == 2:
if user.has_perm('sql.sql_review'):
result = True
elif workflow_type == 3:
if user.has_perm('sql.archive_review'):
result = True
return result
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
if not __notify_cnf_status():
return None
sys_config = SysConfig()
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url', 'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url, audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow.engineer_display,
workflow.group_name,
workflow.instance.instance_name,
workflow.db_name,
audit_auth_group,
workflow.workflow_name,
url,
re.sub('[\r\n\f]{2,}', '\n', workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'], group_id=workflow.group_id)
# 处理接收人
dingding_webhook = ResourceGroup.objects.get(group_id=workflow.group_id).ding_webhook
feishu_webhook = ResourceGroup.objects.get(group_id=workflow.group_id).feishu_webhook
qywx_webhook = ResourceGroup.objects.get(group_id=workflow.group_id).qywx_webhook
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc, dingding_webhook=dingding_webhook, feishu_webhook=feishu_webhook,
qywx_webhook=qywx_webhook)
# DDL通知
if sys_config.get('ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name,
workflow.instance.instance_name,
workflow.db_name,
workflow.workflow_name,
url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
ddl_notify_auth_group = sys_config.get('ddl_notify_auth_group', '').split(',')
msg_to = Users.objects.filter(groups__name__in=ddl_notify_auth_group)
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
wx_status = sys_config.get('wx')
if not sys_config.get('mail') and not sys_config.get('ding') and not sys_config.get('ding_to_person') \
and not wx_status:
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url,
audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.engineer_display, workflow.group_name, audit_auth_group,
workflow.workflow_name, url,
re.sub(
'[\r\n\f]{2,}', '\n',
workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'],
group_id=workflow.group_id)
# 处理接收人
webhook_url = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc, webhook_url=webhook_url)
# DDL通知
if sys_config.get(
'ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name, workflow.instance.instance_name,
workflow.db_name, workflow.workflow_name, url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
msg_to = Users.objects.filter(
groups__name=sys_config.get('ddl_notify_auth_group'))
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc)
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)
# 是否可执行 TODO 这几个判断方法入参都修改为workflow对象，可减少多次数据库交互
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_detail = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
audit_id = audit_detail.audit_id
last_operation_info = Audit.logs(audit_id=audit_id).latest('id').operation_info
# 等待审批的展示当前全部审批人
if workflow_detail.status == 'workflow_manreviewing':
auth_group_name = Group.objects.get(id=audit_detail.current_audit).name
current_audit_users = auth_group_users([auth_group_name], audit_detail.group_id)
current_audit_users_display = [user.display for user in current_audit_users]
last_operation_info += '，当前审批人：' + ','.join(current_audit_users_display)
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 notify_for_audit(audit_id, **kwargs):
"""
工作流消息通知，不包含工单执行结束的通知
:param audit_id:
:param kwargs:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
if not __notify_cnf_status():
return None
sys_config = SysConfig()
# 获取审核信息
audit_detail = Audit.detail(audit_id=audit_id)
audit_id = audit_detail.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
base_url = sys_config.get('archery_base_url', 'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(base_url=base_url, audit_id=audit_detail.audit_id)
workflow_id = audit_detail.workflow_id
workflow_type = audit_detail.workflow_type
status = audit_detail.current_status
workflow_title = audit_detail.workflow_title
workflow_from = audit_detail.create_user_display
group_name = audit_detail.group_name
dingding_webhook = ResourceGroup.objects.get(group_id=audit_detail.group_id).ding_webhook
feishu_webhook = ResourceGroup.objects.get(group_id=audit_detail.group_id).feishu_webhook
qywx_webhook = ResourceGroup.objects.get(group_id=audit_detail.group_id).qywx_webhook
# 获取当前审批和审批流程
workflow_auditors, current_workflow_auditors = Audit.review_info(audit_detail.workflow_id,
audit_detail.workflow_type)
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = ' '
if workflow_detail.priv_type == 1:
workflow_content = '''数据库清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
datetime.datetime.strftime(workflow_detail.valid_date, '%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
elif workflow_detail.priv_type == 2:
db_name = workflow_detail.db_list
workflow_content = '''数据库：{}\n表清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
workflow_detail.table_list,
datetime.datetime.strftime(workflow_detail.valid_date, '%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
else:
workflow_content = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = workflow_detail.db_name
workflow_content = re.sub('[\r\n\f]{2,}', '\n',
workflow_detail.sqlworkflowcontent.sql_content[0:500].replace('\r', ''))
elif workflow_type == WorkflowDict.workflow_type['archive']:
workflow_type_display = WorkflowDict.workflow_type['archive_display']
workflow_detail = ArchiveConfig.objects.get(pk=workflow_id)
instance = workflow_detail.src_instance.instance_name
db_name = workflow_detail.src_db_name
workflow_content = '''归档表：{}\n归档模式：{}\n归档条件：{}\n'''.format(
workflow_detail.src_table_name,
workflow_detail.mode,
workflow_detail.condition)
else:
raise Exception('工单类型不正确')
# 准备消息格式
if status == WorkflowDict.workflow_status['audit_wait']: # 申请阶段
msg_title = "[{}]新的工单申请#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = Group.objects.get(id=audit_detail.current_audit).name
msg_to = auth_group_users([auth_group_names], audit_detail.group_id)
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from,
group_name,
instance,
db_name,
workflow_auditors,
current_workflow_auditors,
workflow_title,
workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_success']: # 审核通过
msg_title = "[{}]工单审核通过#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from,
group_name,
instance,
db_name,
workflow_auditors,
workflow_title,
workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_reject']: # 审核驳回
msg_title = "[{}]工单被驳回#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
instance,
db_name,
workflow_title,
workflow_url,
re.sub('[\r\n\f]{2,}', '\n', workflow_audit_remark))
elif status == WorkflowDict.workflow_status['audit_abort']: # 审核取消，通知所有审核人
msg_title = "[{}]提交人主动终止工单#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = [Group.objects.get(id=auth_group_id).name for auth_group_id in
audit_detail.audit_auth_groups.split(',')]
msg_to = auth_group_users(auth_group_names, audit_detail.group_id)
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n终止原因：{}'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from,
group_name,
instance,
db_name,
workflow_title,
workflow_url,
re.sub('[\r\n\f]{2,}', '\n', workflow_audit_remark))
else:
raise Exception('工单状态不正确')
logger.info(f"通知Debug{msg_to}{msg_cc}")
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc, feishu_webhook=feishu_webhook, dingding_webhook=dingding_webhook,
qywx_webhook=qywx_webhook)
def notify_for_audit(audit_id, **kwargs):
"""
工作流消息通知，不包含工单执行结束的通知
:param audit_id:
:param kwargs:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
if not sys_config.get('mail') and not sys_config.get('ding'):
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取审核信息
audit_detail = Audit.detail(audit_id=audit_id)
audit_id = audit_detail.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(
base_url=base_url, audit_id=audit_detail.audit_id)
msg_cc_email = kwargs.get('email_cc', [])
workflow_id = audit_detail.workflow_id
workflow_type = audit_detail.workflow_type
status = audit_detail.current_status
workflow_title = audit_detail.workflow_title
workflow_from = audit_detail.create_user_display
group_name = audit_detail.group_name
webhook_url = ResourceGroup.objects.get(
group_id=audit_detail.group_id).ding_webhook
# 获取当前审批和审批流程
workflow_auditors, current_workflow_auditors = Audit.review_info(
audit_detail.workflow_id, audit_detail.workflow_type)
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(
apply_id=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = ''
if workflow_detail.priv_type == 1:
workflow_content = '''数据库清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
elif workflow_detail.priv_type == 2:
db_name = workflow_detail.db_list
workflow_content = '''数据库：{}\n表清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list, workflow_detail.table_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
else:
workflow_content = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = workflow_detail.db_name
workflow_content = re.sub(
'[\r\n\f]{2,}', '\n',
workflow_detail.sqlworkflowcontent.sql_content[0:500].replace(
'\r', ''))
else:
raise Exception('工单类型不正确')
# 准备消息格式
if status == WorkflowDict.workflow_status['audit_wait']: # 申请阶段
msg_title = "[{}]新的工单申请#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = Group.objects.get(
id=audit_detail.current_audit).name
msg_to = auth_group_users([auth_group_names], audit_detail.group_id)
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_auditors,
current_workflow_auditors, workflow_title, workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_success']: # 审核通过
msg_title = "[{}]工单审核通过#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_auditors,
workflow_title, workflow_url, workflow_content)
elif status == WorkflowDict.workflow_status['audit_reject']: # 审核驳回
msg_title = "[{}]工单被驳回#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
# 消息内容
msg_content = '''发起时间：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
instance, db_name, workflow_title, workflow_url,
workflow_audit_remark)
elif status == WorkflowDict.workflow_status['audit_abort']: # 审核取消，通知所有审核人
msg_title = "[{}]提交人主动终止工单#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = [
Group.objects.get(id=auth_group_id).name
for auth_group_id in audit_detail.audit_auth_groups.split(',')
]
msg_to = auth_group_users(auth_group_names, audit_detail.group_id)
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n终止原因：{}'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_title,
workflow_url, workflow_audit_remark)
else:
raise Exception('工单状态不正确')
# 处理接收人信息
msg_to_email = [user.email for user in msg_to if user.email]
# 发送通知
msg_sender = MsgSender()
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
msg_to_email,
list_cc_addr=msg_cc_email)
if sys_config.get('ding'):
if webhook_url:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
def send_msg(workflow_detail):
mail_sender = MsgSender()
sys_config = SysConfig()
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow_detail.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow_detail.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url,
audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'],
workflow_detail.status, audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.engineer_display, workflow_detail.group_name,
audit_auth_group, workflow_detail.workflow_name, url,
re.sub('[\r\n\f]{2,}', '\n',
workflow_detail.sql_content[0:500].replace('\r', '')))
if sys_config.get('mail'):
# 邮件通知申请人，抄送DBA
list_to_addr = [
email['email'] for email in Users.objects.filter(
username=workflow_detail.engineer).values('email')
]
list_cc_addr = [
email['email'] for email in
auth_group_users(auth_group_names=['DBA'],
group_id=workflow_detail.group_id).values('email')
]
logger.debug('发送执行结果通知，消息audit_id={}'.format(audit_id))
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(
msg_title, list_to_addr + list_cc_addr, msg_content))
mail_sender.send_email(msg_title,
msg_content,
list_to_addr,
list_cc_addr=list_cc_addr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(
group_id=workflow_detail.group_id).ding_webhook
MsgSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
# DDL通知
if sys_config.get('mail') and sys_config.get('ddl_notify_auth_group', None) \
and workflow_detail.status == '已正常结束':
# 判断上线语句是否存在DDL，存在则通知相关人员
sql_content = workflow_detail.sql_content
# 删除注释语句
sql_content = ''.join(
map(
lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub(
'', x, count=1), sql_content.splitlines(1))).strip()
# 去除空行
sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
# 匹配DDL语句CREATE、ALTER（排除索引变更）、DROP、TRUNCATE、RENAME
send = 0
for statement in sqlparse.split(sql_content):
# alter语法
if re.match(
r"^alter\s+table\s+\S+\s+(add|alter|change|drop|rename|modify)\s+(?!.*(index|key|unique))",
statement.strip().lower()):
send = 1
break
# create语法
elif re.match(r"^create\s+(temporary\s+)?(database|schema|table)",
statement.strip().lower()):
send = 1
break
# drop语法
elif re.match(r"^drop|^rename|^truncate",
statement.strip().lower()):
send = 1
break
if send == 1:
# 消息内容通知
msg_title = '[archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow_detail.engineer).display,
workflow_detail.group_name, workflow_detail.instance_name,
workflow_detail.db_name, workflow_detail.workflow_name, url,
workflow_detail.sql_content[0:500])
# 获取通知成员
msg_to = [
email['email']
for email in Users.objects.filter(groups__name=sys_config.get(
'ddl_notify_auth_group')).values('email')
]
# 发送
logger.debug('发送DDL通知，消息audit_id={}'.format(audit_id))
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(
msg_title, msg_to, msg_content))
mail_sender.send_email(msg_title, msg_content, msg_to)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
if not sys_config.get('mail') and not sys_config.get('ding'):
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url,
audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.engineer_display, workflow.group_name, audit_auth_group,
workflow.workflow_name, url,
re.sub(
'[\r\n\f]{2,}', '\n',
workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
list_to_addr = [
email['email'] for email in Users.objects.filter(
username=workflow.engineer).values('email')
]
list_cc_addr = [
email['email'] for email
in auth_group_users(auth_group_names=['DBA'],
group_id=workflow.group_id).values('email')
]
msg_email_reciver = list_to_addr + list_cc_addr
# 判断是发送钉钉还是发送邮件
msg_sender = MsgSender()
logger.info('发送消息通知，消息audit_id={}'.format(audit_id))
logger.info('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
list_to_addr,
list_cc_addr=list_cc_addr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
MsgSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
# DDL通知
if sys_config.get('mail') and sys_config.get(
'ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name, workflow.instance.instance_name,
workflow.db_name, workflow.workflow_name, url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
msg_to = [
email['email']
for email in Users.objects.filter(groups__name=sys_config.get(
'ddl_notify_auth_group')).values('email')
]
# 发送
logger.info('发送DDL通知，消息audit_id={}'.format(audit_id))
logger.info('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(
msg_title, msg_to, msg_content))
msg_sender.send_email(msg_title, msg_content, msg_to)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
wx_status = sys_config.get('wx')
wx_msg_content = ''
if not sys_config.get('mail') and not sys_config.get('ding') and not sys_config.get('ding_to_person') \
and not wx_status:
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url', 'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url, audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.engineer_display,
workflow.group_name,
audit_auth_group,
workflow.workflow_name,
url,
re.sub('[\r\n\f]{2,}', '\n', workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
if wx_status:
wx_msg_content = "[工单执行完毕（点击查看）]({})\n" \
">发起人：<font color=\"comment\">{}</font>\n" \
">组：<font color=\"comment\">{}</font>\n" \
">审批流程：<font color=\"comment\">{}</font>\n" \
">工单名称：<font color=\"comment\">{}</font>\n" \
">审批流程：<font color=\"comment\">{}</font>\n".format(
url, workflow.engineer_display, workflow.group_name, audit_auth_group, workflow.workflow_name,
re.sub('[\r\n\f]{2,}', '\n', workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'], group_id=workflow.group_id)
# 处理接收人信息
msg_to_email = [user.email for user in msg_to if user.email]
msg_cc_email = [user.email for user in msg_cc if user.email]
msg_to_ding_user = [user.ding_user_id for user in msg_to if user.ding_user_id]
# 判断是发送钉钉还是发送邮件
msg_sender = MsgSender()
if sys_config.get('mail'):
msg_sender.send_email(msg_title, msg_content, msg_to_email, list_cc_addr=msg_cc_email)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(group_id=workflow.group_id).ding_webhook
if webhook_url:
MsgSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if sys_config.get('ding_to_person') and msg_to_ding_user:
msg_sender.send_ding2user(msg_to_ding_user, msg_title + '\n' + msg_content)
if wx_status:
msg_to_wx_user = []
for user in msg_to:
if user.wx_user_id:
msg_to_wx_user.append(user.wx_user_id)
else:
msg_to_wx_user.append(user.username)
msg_sender.send_wx2user(wx_msg_content, msg_to_wx_user)
# DDL通知
if sys_config.get('mail') and sys_config.get('ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name,
workflow.instance.instance_name,
workflow.db_name,
workflow.workflow_name,
url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
msg_to = Users.objects.filter(groups__name=sys_config.get('ddl_notify_auth_group'))
# 处理接收人信息
msg_to_email = [user.email for user in msg_to]
# 发送
msg_sender.send_email(msg_title, msg_content, msg_to_email)
def notify(audit_info, msg_type=0, **kwargs):
audit_id = audit_info.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
workflow_url = kwargs.get('workflow_url', '')
msg_email_cc = kwargs.get('email_cc', [])
workflow_id = audit_info.workflow_id
workflow_type = audit_info.workflow_type
status = audit_info.current_status
workflow_title = audit_info.workflow_title
workflow_from = audit_info.create_user_display
group_name = audit_info.group_name
webhook_url = ResourceGroup.objects.get(
group_id=audit_info.group_id).ding_webhook
# 获取当前审批和审批流程
from sql.utils.workflow_audit import Audit
workflow_auditors, current_workflow_auditors = Audit.review_info(
audit_info.workflow_id, audit_info.workflow_type)
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(
apply_id=workflow_id)
if workflow_detail.priv_type == 1:
workflow_content = '''数据库清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
elif workflow_detail.priv_type == 2:
workflow_content = '''数据库：{}\n表清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list, workflow_detail.table_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
else:
workflow_content = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
workflow_content = re.sub(
'[\r\n\f]{2,}', '\n',
workflow_detail.sql_content[0:500].replace('\r', ''))
else:
raise Exception('工单类型不正确')
# 准备消息格式
if status == WorkflowDict.workflow_status['audit_wait']: # 申请阶段
msg_title = "[{}]新的工单申请#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = Group.objects.get(id=audit_info.current_audit).name
msg_email_reciver = [
user.email for user in auth_group_users([auth_group_names],
audit_info.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_name, workflow_auditors,
current_workflow_auditors, workflow_title, workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_success']: # 审核通过
msg_title = "[{}]工单审核通过#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_email_reciver = [
Users.objects.get(username=audit_info.create_user).email
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_name, workflow_auditors, workflow_title,
workflow_url, workflow_content)
elif status == WorkflowDict.workflow_status['audit_reject']: # 审核驳回
msg_title = "[{}]工单被驳回#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_email_reciver = [
Users.objects.get(username=audit_info.create_user).email
]
# 消息内容
msg_content = '''工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
workflow_title, workflow_url, workflow_audit_remark)
elif status == WorkflowDict.workflow_status['audit_abort']: # 审核取消，通知所有审核人
msg_title = "[{}]提交人主动终止工单#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = [
Group.objects.get(id=auth_group_id).name
for auth_group_id in audit_info.audit_auth_groups.split(',')
]
msg_email_reciver = [
user.email
for user in auth_group_users(auth_group_names, audit_info.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n工单名称：{}\n工单地址：{}\n提醒：提交人主动终止流程'''.format(
workflow_from, group_name, workflow_title, workflow_url)
else:
raise Exception('工单状态不正确')
# 判断是发送钉钉还是发送邮件
msg_sender = MsgSender()
logger.debug('发送消息通知，消息audit_id={}'.format(audit_id))
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if msg_type == 0:
sys_config = SysConfig()
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
if sys_config.get('ding'):
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
elif msg_type == 1:
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
elif msg_type == 2:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
if not sys_config.get('mail') and not sys_config.get('ding'):
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url,
audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
# msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n正常执行：请验证\n'''.format(
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n执行结束：请验证\n'''.format(
workflow.engineer_display, workflow.group_name, audit_auth_group,
workflow.workflow_name, url)
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'],
group_id=workflow.group_id)
# 处理接收人信息
msg_to_email = [user.email for user in msg_to if user.email]
msg_cc_email = [user.email for user in msg_cc if user.email]
to_user_list = []
to_user_list.append(workflow.engineer)
# 判断是发送钉钉还是发送邮件
msg_sender = MsgSender()
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
msg_to_email,
list_cc_addr=msg_cc_email)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
if webhook_url:
MsgSender.send_ding(webhook_url, msg_title + '\n' + msg_content,
to_user_list)
# DDL通知
if sys_config.get('mail') and sys_config.get(
'ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name, workflow.instance.instance_name,
workflow.db_name, workflow.workflow_name, url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
msg_to = Users.objects.filter(
groups__name=sys_config.get('ddl_notify_auth_group'))
# 处理接收人信息
msg_to_email = [user.email for user in msg_to]
# 发送
msg_sender.send_email(msg_title, msg_content, msg_to_email)
