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 = Workflow.auditinfobyworkflow_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
return result
def send_msg(workflowDetail, url):
mailSender = MailSender()
sys_config = SysConfig().sys_config
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(workflowDetail.id, 2)
# 如果执行完毕了，则根据配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
msg_title = "[{}]工单{}#{}".format(WorkflowDict.workflow_type['sqlreview_display'], workflowDetail.status,
workflowDetail.id)
msg_content = '''发起人：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflowDetail.engineer_display, audit_auth_group, workflowDetail.workflow_name, url,
workflowDetail.sql_content[0:500])
if sys_config.get('mail') == 'true':
# 邮件通知申请人，审核人，抄送DBA
notify_users = workflowDetail.review_man.split(',')
notify_users.append(workflowDetail.engineer)
listToAddr = [email['email'] for email in Users.objects.filter(username__in=notify_users).values('email')]
listCcAddr = [email['email'] for email in
auth_group_users(auth_group_names=['DBA'], group_id=workflowDetail.group_id).values('email')]
mailSender.send_email(msg_title, msg_content, listToAddr, listCcAddr=listCcAddr)
if sys_config.get('ding') == 'true':
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = SqlGroup.objects.get(group_id=workflowDetail.group_id).ding_webhook
MailSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
def send_msg(workflowDetail, url):
mailSender = MailSender()
sys_config = SysConfig().sys_config
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflowDetail.id, 2)
audit_id = Workflow.auditinfobyworkflow_id(workflowDetail.id, 2).audit_id
# 如果执行完毕了，则根据配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'], workflowDetail.status,
audit_id)
msg_content = '''发起人：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflowDetail.engineer_display, audit_auth_group,
workflowDetail.workflow_name, url,
re.sub('[\r\n\f]{2,}', '\n',
workflowDetail.sql_content[0:500].replace('\r', '')))
if sys_config.get('mail'):
# 邮件通知申请人，审核人，抄送DBA
notify_users = workflowDetail.audit_auth_groups.split(',')
notify_users.append(workflowDetail.engineer)
listToAddr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
listCcAddr = [
email['email'] for email in
auth_group_users(auth_group_names=['DBA'],
group_id=workflowDetail.group_id).values('email')
]
mailSender.send_email(msg_title,
msg_content,
listToAddr,
listCcAddr=listCcAddr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = SqlGroup.objects.get(
group_id=workflowDetail.group_id).ding_webhook
MailSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if sys_config.get('mail') and sys_config.get('ddl_notify_auth_group', None) \
and workflowDetail.status == '已正常结束':
# 判断上线语句是否存在DDL，存在则通知相关人员
sql_content = workflowDetail.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 row in sql_content.strip(';').split(';'):
# alter语法
if re.match(
r"^alter\s+table\s+\S+\s+(add|alter|change|drop|rename|modify)\s+(?!.*(index|key|unique))",
row.strip().lower()):
send = 1
break
# create语法
elif re.match(r"^create\s+(temporary\s+)?(database|schema|table)",
row.strip().lower()):
send = 1
break
# drop语法
elif re.match(r"^drop", row.strip().lower()):
send = 1
break
# rename语法
elif re.match(r"^rename", row.strip().lower()):
send = 1
break
# truncate语法
elif re.match(r"^truncate", row.strip().lower()):
send = 1
break
if send == 1:
# 消息内容通知
msg_title = '[Archer]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflowDetail.engineer).display,
workflowDetail.group_name, workflowDetail.instance_name,
workflowDetail.db_name, workflowDetail.workflow_name, url,
workflowDetail.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')
]
# 发送
mailSender.send_email(msg_title, msg_content, msg_to)
def _send(audit_id, msg_type, **kwargs):
msg_sender = MailSender()
sys_config = SysConfig().sys_config
audit_info = WorkflowAudit.objects.get(audit_id=audit_id)
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
workflow_url = kwargs.get('workflow_url')
webhook_url = SqlGroup.objects.get(group_id=audit_info.group_id).ding_webhook
audit_info = WorkflowAudit.objects.get(workflow_id=workflow_id, workflow_type=workflow_type)
if audit_info.audit_auth_groups == '':
workflow_auditors = '无需审批'
else:
try:
workflow_auditors = '->'.join([Group.objects.get(id=auth_group_id).name for auth_group_id in
audit_info.audit_auth_groups.split(',')])
except Exception:
workflow_auditors = audit_info.audit_auth_groups
if audit_info.current_audit == '-1':
current_workflow_auditors = None
else:
try:
current_workflow_auditors = Group.objects.get(id=audit_info.current_audit).name
except Exception:
current_workflow_auditors = audit_info.current_audit
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
workflow_audit_remark = ''
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)
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
workflow_audit_remark = workflow_detail.audit_remark
workflow_content = workflow_detail.sql_content
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)]
# 抄送对象
email_cc = kwargs.get('email_cc', [])
msg_email_cc = email_cc
msg_content = '''发起人：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from,
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_email_cc = kwargs.get('email_cc', [])
msg_content = '''发起人：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from,
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_email_cc = []
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_email_cc = []
msg_content = '''发起人：{}\n工单名称：{}\n工单地址：{}\n提醒：提交人主动终止流程'''.format(
workflow_from,
workflow_title,
workflow_url)
else:
raise Exception('工单状态不正确')
if isinstance(msg_email_reciver, str):
msg_email_reciver = [msg_email_reciver]
if isinstance(msg_email_cc, str):
msg_email_cc = [msg_email_cc]
# 判断是发送钉钉还是发送邮件
try:
if msg_type == 0:
if sys_config.get('mail') == 'true':
msg_sender.send_email(msg_title, msg_content, msg_email_reciver, listCcAddr=msg_email_cc)
if sys_config.get('ding') == 'true':
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if msg_type == 1:
if sys_config.get('mail') == 'true':
msg_sender.send_email(msg_title, msg_content, msg_email_reciver, listCcAddr=msg_email_cc)
elif msg_type == 2:
if sys_config.get('ding') == 'true':
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
except Exception:
logger.error(traceback.format_exc())
def _send(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 = SqlGroup.objects.get(
group_id=audit_info.group_id).ding_webhook
# 获取当前审批和审批流程
from sql.utils.workflow import Workflow
workflow_auditors, current_workflow_auditors = Workflow.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 = MailSender()
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if msg_type == 0:
sys_config = SysConfig().sys_config
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 _send(audit_id, msg_type, **kwargs):
try:
audit_info = WorkflowAudit.objects.get(audit_id=audit_id)
except Exception:
logger.error('工单信息获取错误,尝试关闭连接重新获取,audit_id={}'.format(audit_id))
connection.close()
audit_info = WorkflowAudit.objects.get(audit_id=audit_id)
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
workflow_url = kwargs.get('workflow_url')
webhook_url = SqlGroup.objects.get(
group_id=audit_info.group_id).ding_webhook
# 获取审批流程
if audit_info.audit_auth_groups == '':
workflow_auditors = '无需审批'
else:
try:
workflow_auditors = '->'.join([
Group.objects.get(id=auth_group_id).name
for auth_group_id in audit_info.audit_auth_groups.split(',')
])
except Exception:
workflow_auditors = audit_info.audit_auth_groups
# 获取当前审批节点
if audit_info.current_audit == '-1':
current_workflow_auditors = None
else:
try:
current_workflow_auditors = Group.objects.get(
id=audit_info.current_audit).name
except Exception:
current_workflow_auditors = audit_info.current_audit
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(
apply_id=workflow_id)
try:
workflow_audit_remark = WorkflowAuditDetail.objects.filter(
audit_id=audit_id).latest('audit_time').remark
except Exception:
workflow_audit_remark = ''
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_audit_remark = workflow_detail.audit_remark
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)
]
# 抄送对象
email_cc = kwargs.get('email_cc', [])
msg_email_cc = email_cc
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_email_cc = kwargs.get('email_cc', [])
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_email_cc = []
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_email_cc = []
msg_content = '''发起人：{}\n组：{}\n工单名称：{}\n工单地址：{}\n提醒：提交人主动终止流程'''.format(
workflow_from, group_name, workflow_title, workflow_url)
else:
raise Exception('工单状态不正确')
if isinstance(msg_email_reciver, str):
msg_email_reciver = [msg_email_reciver]
if isinstance(msg_email_cc, str):
msg_email_cc = [msg_email_cc]
# 判断是发送钉钉还是发送邮件
msg_sender = MailSender()
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if msg_type == 0:
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
listCcAddr=msg_email_cc)
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,
listCcAddr=msg_email_cc)
elif msg_type == 2:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
