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)