{% extends "base.html" %}
{% block content %}
<div class="row clearfix">
<div class="col-md-12">
<div class="panel panel-default">
<div class="panel-heading">
数据导出
</div>
<div class="panel-body">
<form id="form-submitsql" class="form-horizontal">
{% csrf_token %}
<input type="hidden" id="workflow_id" name="workflow_id"/>
<div class="col-md-9 column">
<pre id="sql_content_editor" style="min-height:450px"></pre>
<!-- 校验提示 -->
<div class="validation-notice" id="validationNotice">
<div class="notice-icon" id="noticeIcon">
</div>
<div class="notice-content">
<div class="notice-title">行数统计中...</div>
<div class="notice-message">正在校验SQL语法并统计结果行数，请稍候...</div>
</div>
<button class="close-notice" id="closeNotice" title="关闭">
×
</button>
<div class="progress-bar">
<div class="progress-bar-inner"></div>
</div>
</div>
</div>
<div style="display: none" class="col-md-8 column">
<textarea id="sql_content" name="sql_content" class="form-control" data-name="SQL内容"
data-placeholder="SQL内容不能为空！"
placeholder="请在此提交SQL，请以分号结尾。" rows=20
required></textarea>
</div>
<div class="col-md-3 column">
<div class="form-group">
<input id="sql-upload" name="sql-upload" accept=".sql" type="file" class="file-loading">
</div>
<div class="form-group">
<input id="workflow_name" type="text" autocomplete="off" name="workflow_name"
class="form-control"
data-name="上线单名称" data-placeholder="请输入上线单名称!"
placeholder="请输入上线单名称，如:XX项目会员功能建表"
required>
</div>
<div class="form-group toggleable">
<select id="export_format" name="export_format"
class="selectpicker show-tick form-control bs-select-hidden"
data-name="离线导出数据格式" data-placeholder="请选择导出数据格式！"
title="请选择导出数据格式"
data-live-search="true" data-live-search-placeholder="搜索您所在的组" required>
<option value="csv">CSV</option>
<option value="xlsx">Excel</option>
<option value="sql">SQL</option>
<option value="json">JSON</option>
<option value="xml">XML</option>
</select>
</div>
<div class="form-group">
<select id="group_name" name="group_name"
class="selectpicker show-tick form-control bs-select-hidden"
data-name="组" data-placeholder="请选择组！"
title="请选择组"
data-live-search="true" data-live-search-placeholder="搜索您所在的组" required>
{% for group in group_list %}
<option value="{{ group.group_name }}"
group-id="{{ group.group_id }}">{{ group.group_name }}</option>
{% endfor %}
</select>
</div>
<div class="form-group">
<select id="instance_name" name="instance_name"
class="selectpicker show-tick form-control bs-select-hidden"
data-name="实例" data-placeholder="请选择实例！"
title="请选择实例"
data-live-search="true" data-live-search-placeholder="搜索您所在组的实例" required>
{% for name, engine in engines.items %}
<optgroup id="optgroup-{{ name }}" label="{{ engine.name }}"></optgroup>
{% endfor %}
</select>
</div>
<div class="form-group">
<select id="db_name" name="db_name"
class="form-control selectpicker show-tick bs-select-hidden"
data-name="数据库" data-placeholder="请选择数据库！"
data-live-search="true" data-live-search-placeholder="搜索要提交的数据库"
title="请选择数据库"
required>
<option value="is-empty" disabled="" selected="selected">请选择数据库:</option>
</select>
</div>
{% if enable_backup_switch %}
<div id="div-backup" class="form-group">
<select id="is_backup" name="is_backup"
class="selectpicker show-tick form-control bs-select-hidden"
data-name="是否选择备份"
title="请选择是否需要备份"
data-placeholder="请选择是否要备份" required>
<option value=True selected="selected">备份SQL</option>
<option value=False>不备份SQL</option>
</select>
</div>
{% endif %}
<!--可执行范围时间-->
<div class='form-group'>
<input type="hidden" id="run_date_start" name="run_date_start">
<input type="hidden" id="run_date_end" name="run_date_end">
<div class='input-group date'>
<input type="text" id="run_date_range" readonly
value="请选择可执行时间范围"
style="background-color: #fff;color: #999 "
class="form-control "/>
<span class="input-group-addon"><span
class="glyphicon glyphicon-time"></span></span>
</div>
</div>
<!--审批流程-->
<input type="hidden" id="workflow_auditors" name="workflow_auditors" data-name="审批流程"
data-placeholder="请配置审批流程！" required>
<div id="div-workflow_auditors" class="form-group" style="display: none">
<p class="bg-primary">   审批流程：<b id="workflow_auditors_display"></b></p>
</div>
<!--button-->
<div class="form-group">
<button type="button" id="btn-format" class="btn btn-info">美化</button>
<button type="button" id="btn-autoreview" data-loading-text="检测中..."
class="btn btn-danger">SQL检测
</button>
<button type="button" id="btn-submitsql" data-loading-text="提交中..."
class="btn btn-success" disabled>
导出提交
</button>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
{% endblock content %}
{% block js %}
{% load static %}
<link href="{% static 'bootstrap-fileinput/css/fileinput.min.css' %}" rel="stylesheet">
<link href="{% static 'daterangepicker/css/daterangepicker.css' %}" rel="stylesheet" type="text/css"/>
<script src="{% static 'ace/ace.js' %}"></script>
<script src="{% static 'ace/ext-language_tools.js' %}"></script>
<script src="{% static 'ace/mode-mysql.js' %}"></script>
<script src="{% static 'ace/theme-github.js' %}"></script>
<script src="{% static 'ace/snippets/mysql.js' %}"></script>
<script src="{% static 'ace/ace-init.js' %}"></script>
<script src="{% static 'bootstrap-fileinput/js/fileinput.min.js' %}"></script>
<script src="{% static 'bootstrap-fileinput/js/locales/zh.js' %}"></script>
<script src="{% static 'daterangepicker/js/moment.min.js' %}"></script>
<script src="{% static 'daterangepicker/js/daterangepicker.js' %}"></script>
<!-- 校验提示 notice/css/notice.css-->
<script>
const validationNotice = document.getElementById('validationNotice');
const closeNotice = document.getElementById('closeNotice');
const successMessage = document.getElementById('successMessage');
const noticeIcon = document.getElementById('noticeIcon');
const progressBar = document.querySelector('.progress-bar-inner');
validationNotice.style.display = 'none';
let timeoutId;
// 重置校验通知
function resetValidationNotice() {
clearTimeout(timeoutId);
closeNotice.style.display = 'none';
validationNotice.style.display = 'flex';
noticeIcon.classList.remove('icon-loading','icon-success','icon-error');
noticeIcon.classList.add('icon-loading');
validationNotice.className = 'validation-notice visible';
validationNotice.querySelector('.notice-title').textContent = "行数统计中...";
validationNotice.querySelector('.notice-message').textContent = "正在校验SQL语法并统计结果行数，请稍候...";
validationNotice.classList.remove('status-success', 'status-error', 'status-warning');
}
// 关闭提示按钮
closeNotice.addEventListener('click', function(e) {
e.preventDefault();
validationNotice.style.display = 'none';
});
function showStatusNotice(options = {}) {
// 解构配置参数并提供默认值
const {
element = validationNotice, // 默认使用validationNotice元素
noticeIconClass = '', // 通知图标元素
statusClass = 'status-error', // 状态类名
title = '校验失败', // 默认标题
message = '', // 默认消息
params = {}, // 消息中的替换参数
delay = 5000, // 默认5秒后消失
} = options;
clearTimeout(timeoutId);
// 替换消息中的占位符 {key} => value
const formatMessage = (msg, values) => {
return msg.replace(/\{(\w+)\}/g, (_, key) => values[key] || '');
};
const formattedMessage = formatMessage(message, params);
// 更新显示内容
closeNotice.style.display = 'flex';
noticeIcon.classList.remove('icon-loading');
noticeIcon.classList.add(noticeIconClass);
element.classList.add(statusClass);
element.querySelector('.notice-title').textContent = title;
element.querySelector('.notice-message').textContent = formattedMessage;
// 进度条
progressBar.style.width = '100%';
progressBar.style.transition = 'none';
void progressBar.offsetWidth;
progressBar.style.transition = `width ${delay}ms linear`;
progressBar.style.width = '0'
setAutoClose(delay);
}
// 设置自动隐藏
function setAutoClose(delay) {
clearTimeout(timeoutId);
timeoutId = setTimeout(() => {
validationNotice.style.display = 'none';
validationNotice.classList.remove('visible');
}, delay);
}
// 鼠标悬停暂停
validationNotice.addEventListener('mouseenter', () => {
if (validationNotice.classList.contains('status-success') || validationNotice.classList.contains('status-error')) {
clearTimeout(timeoutId);
progressBar.style.transition = 'none';
progressBar.style.width = '100%';
}
});
// 鼠标离开重新倒计时关闭
validationNotice.addEventListener('mouseleave', () => {
if (validationNotice.classList.contains('status-success') || validationNotice.classList.contains('status-error')) {
const remainingWidth = progressBar.offsetWidth;
const delay = (remainingWidth / validationNotice.offsetWidth) * 5000;
progressBar.style.transition = `width ${delay}ms linear`;
progressBar.style.width = '0';
setAutoClose(delay);
}
});
</script>
<!--初始化时间控件 -->
<script>
$("#run_date_range").daterangepicker({
timePicker: true,
timePicker24Hour: true,
autoApply: true,
autoUpdateInput: false,
opens: "left",
drops: "up",
minDate: moment().startOf('minutes'),
startDate: moment().startOf('hour'),
endDate: moment().startOf('hour'),
locale: {
"applyLabel": "确定",
"cancelLabel": "清空",
"daysOfWeek": ["日", "一", "二", "三", "四", "五", "六"],
"monthNames": ["一月", "二月", "三月", "四月", "五月", "六", "七月", "八月", "九月", "十月", "十一月", "十二月"],
"firstDay": 1
},
}).on('apply.daterangepicker', function (ev, picker) {
$(this).css("color", "#333");
$("#run_date_start").val(picker.startDate.format('YYYY-MM-DD HH:mm'));
$("#run_date_end").val(picker.endDate.format('YYYY-MM-DD HH:mm'));
$(this).val(picker.startDate.format('MM-DD HH:mm') + ' / ' + picker.endDate.format('MM-DD HH:mm'));
}).on('cancel.daterangepicker', function (ev, picker) {
$(this).css("color", "#999");
$(this).val('请选择可执行时间范围');
$("#run_date_start").val('');
$("#run_date_end").val('');
});
</script>
<!--ace -->
<script>
// 实例变更获取数据库补全提示
$("#instance_name").change(function () {
var optgroup = $('#instance_name :selected').parent().attr('label');
$("#div-backup").show();
if (optgroup != "MySQL" && optgroup != "Oracle") {
$("#div-backup").hide();
$("#is_backup").val("False");
}
//将数据通过ajax提交给获取db_name
$.ajax({
type: "get",
url: "/instance/instance_resource/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
resource_type: "database"
},
complete: function () {
const urlParams = new URLSearchParams(window.location.search);
const urlSource = urlParams.get('source');
if (urlSource === "resubmit") {
//填充库名
$("#db_name").selectpicker('val', sessionStorage.getItem('editDbname'));
if ($("#db_name").val()) {
$("#db_name").selectpicker().trigger("change");
}
}
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
$("#db_name").empty();
for (var i = 0; i < result.length; i++) {
var name = "<option value=\"" + result[i] + "\">" + result[i] + "</option>";
$("#db_name").append(name);
}
$('#db_name').selectpicker('render');
$('#db_name').selectpicker('refresh');
//自动补全提示
setDbsCompleteData(result)
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
});
//数据库变更获取表名称补全提示
$("#db_name").change(function () {
//将数据通过ajax提交给获取db_name
$.ajax({
type: "get",
url: "/instance/instance_resource/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
db_name: $("#db_name").val(),
resource_type: "table"
},
complete: function () {
},
success: function (data) {
if (data.status === 0) {
//自动补全提示
setTablesCompleteData(data.data)
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
});
</script>
<!--validate -->
<script>
function validateForm(element) {
var result = true;
element.find('[required]').each(
function () {
var fieldElement = $(this);
var fieldId = $(this).attr('id');
//如果为null则设置为''
var value = fieldElement.val() || '';
if (value) {
value = value.trim();
}
if (!value || value === fieldElement.attr('data-placeholder')) {
alert(fieldElement.attr('data-placeholder'));
result = false;
return result;
}
if(fieldId == "workflow_name" && value.length>=50){
alert("上线单名称/工单名称不能超过50个字符！");
result = false;
return result;
}
}
);
return result;
}
//美化
$("#btn-format").click(function () {
var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
if (select_sqlContent) {
var sqlContent = select_sqlContent
} else {
var sqlContent = editor.getValue();
}
var sqlContent = window.sqlFormatter.format(sqlContent);
editor.setValue(sqlContent);
editor.clearSelection();
}
);
</script>
<!--check-->
<script>
// 监听sql文本，若sql发生变化则禁用提交按钮
editor.getSession().on('change', function(e) {
$("#btn-submitsql").prop("disabled", true);
});
// 监听选区变化，若选取变化则禁用提交按钮
editor.getSession().selection.on('changeSelection', function(e) {
$("#btn-submitsql").prop("disabled", true);
});
function checkRunDate(gap = 60) {
// 结束时间与开始时间的间隔不应该太短
var start = $("#run_date_start").val();
var end = $("#run_date_end").val();
if (start && end) {
var realGap = ((new Date(end)).getTime() - (new Date(start)).getTime()) / (1000 * 60)
if (realGap >= gap) {
return true
} else {
alert("可执行时间范围不应该短于" + gap + "分钟");
return false
}
}
return true
}
function removeStartsWith(str,prefix) {
if (typeof str !== 'string' || str === null) {
return str;
}
if (str.startsWith(prefix)) {
return str.substring(prefix.length);
}
return str;
};
$("#btn-autoreview").click(function () {
if (sqlquery_validate()) {
$("#btn-submitsql").prop("disabled", true);
resetValidationNotice()
editor.container.style.pointerEvents = "none";
editor.container.style.opacity = 0.6;
$("#db_name").attr("disabled", true);
$("#instance_name").attr("disabled", true);
$("#group_name").attr("disabled", true);
sqlcount('explain')
}
});
//表单验证
function sqlquery_validate() {
var result = true;
var instance_name = $("#instance_name").val();
var db_name = $("#db_name").val();
var sqlContent = editor.getValue();
var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
if (select_sqlContent) {
sqlContent = select_sqlContent
}
var isOfflineExport = $("#btn-submitsql").is(':hidden') ? 0 : 1;
// 移除单行注释和多行注释后再统计语句数量
let processedSql = sqlContent.replace(/--.*$/gm, ''); // 移除单行注释
processedSql = processedSql.replace(/\/\*[\s\S]*?\*\//gm, ''); // 移除多行注释
var totalSqlCnt = processedSql.split(';')
.map(stmt => stmt.trim()) // 去除每个语句两端的空白
.filter(stmt => stmt.length > 0) // 过滤掉空语句
.length; // 统计语句数量
// 检查SQL开头
let trimmedSQL = sqlContent.trim().toUpperCase();
// 跳过所有开头的注释（单行/多行）
while (true) {
// 跳过单行注释 (--)
if (trimmedSQL.startsWith("--")) {
const nlIndex = trimmedSQL.indexOf('\n');
trimmedSQL = (nlIndex > -1)
? trimmedSQL.substring(nlIndex + 1).trim()
: "";
continue;
}
// 跳过多行注释 (/*...*/)
if (trimmedSQL.startsWith("/*")) {
const endIndex = trimmedSQL.indexOf("*/");
if (endIndex === -1) break; // 未闭合注释不处理
trimmedSQL = trimmedSQL.substring(endIndex + 2).trim();
continue;
}
break;
}
if (!instance_name) {
alert("请选择实例！");
return result = false;
} else if (!db_name) {
alert("请选择数据库！");
return result = false;
} else if (!sqlContent) {
alert("SQL内容不能为空！");
return result = false;
} else if (!trimmedSQL.startsWith("SELECT") && !trimmedSQL.startsWith("WITH")) {
alert("SQL必须是查询语句！");
return result = false;
} else if (isOfflineExport === 1 && totalSqlCnt > 1) {
alert("检测到多个SQL语句，只能提交一个SQL，请选中要提交导出工单的SQL重试。");
return result = false;
}
return result;
}
//count行数统计
function sqlcount(sql) {
var result = true;
var optgroup = $('#instance_name :selected').parent().attr('label');
var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
if (select_sqlContent) {
sqlContent = select_sqlContent
} else {
var sqlContent = editor.getValue();
}
var sqlContent = sqlContent.trim().replace(/;$/, '');
var sqlContent = 'select count(1) from (' + sqlContent + '\n) t'
//提交请求
$.ajax({
type: "post",
url: "/query/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
db_name: $("#db_name").val(),
schema_name: $("#schema_name").val(),
tb_name: $("#table_name").val(),
sql_content: sqlContent,
limit_num: $("#limit_num").val()
},
complete: function () {
$("#db_name").attr("disabled", false);
$("#instance_name").attr("disabled", false);
$("#group_name").attr("disabled", false);
editor.container.style.pointerEvents = "auto";
editor.container.style.opacity = 1;
},
success: function (data) {
validate_data(data);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
}
//SQL提交
$("#btn-submitsql").click(function () {
//获取form对象，判断输入，通过则提交
$("#sql_content").val(editor.getValue());
if (validateForm($("#form-submitsql")) && checkRunDate()) {
resetValidationNotice()
validationNotice.querySelector('.notice-title').textContent = "工单提交中...";
validationNotice.querySelector('.notice-message').textContent = "后台二次校验，请稍候...";
editor.container.style.pointerEvents = "none";
editor.container.style.opacity = 0.6;
sqlSubmit()
}
});
// 校验返回的结果
function validate_data(data) {
var result = data.data;
if (data.status === 0) {
var ActualRows = 0;
var maxExportRows = "{{ max_export_rows }}";
ActualRows = parseFloat(result['rows'][0][0]);
if (ActualRows > maxExportRows) {
$("#btn-submitsql").prop("disabled", true);
showStatusNotice({
noticeIconClass: 'icon-error',
statusClass: 'status-error',
title: '校验失败',
message: '统计查询结果：{ActualRows} 行数据，超过了阈值：{maxExportRows}',
params: { ActualRows: ActualRows, maxExportRows: maxExportRows },
delay: 5000
});
} else if (ActualRows <= maxExportRows) {
$("#btn-submitsql").prop("disabled", false);
showStatusNotice({
noticeIconClass: 'icon-success',
statusClass: 'status-success',
title: '校验成功',
message: '统计查询结果：{ActualRows} 行数据，点击提交导出按钮申请导出。',
params: { ActualRows: ActualRows},
delay: 5000
});
} else {
$("#btn-submitsql").prop("disabled", true);
}
} else {
showStatusNotice({
noticeIconClass: 'icon-error',
statusClass: 'status-error',
title: '校验失败',
message: '错误：{msg}',
params: { msg: data.msg },
delay: 5000
});
}
}
// 提交sql
function sqlSubmit() {
let formData = $('#form-submitsql').serializeArray().reduce(function (obj, item) {
obj[item.name] = item.value;
return obj;
}, {})
var sqlContent = editor.getValue();
var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
if (select_sqlContent) {
sqlContent = select_sqlContent
}
$.ajax({
type: "post",
url: "/api/v1/workflow/",
dataType: "json",
contentType: 'application/json;',
data: JSON.stringify({
workflow: {
workflow_name: formData.workflow_name,
export_format: formData.export_format,
is_offline_export: 1,
group_id: $("#group_name option:selected").attr("group-id"),
instance: $("#instance_name option:selected").attr("instance-id"),
db_name: formData.db_name,
is_backup: 'False',
run_date_start: formData.run_date_start,
run_date_end: formData.run_date_end,
},
sql_content: sqlContent
}
),
beforeSend: function (xhr) {
$('#btn-submitsql').button('loading')
},
complete: function () {
$('#btn-submitsql').button('reset')
editor.container.style.pointerEvents = "auto";
editor.container.style.opacity = 1;
},
success: function (data) {
window.location.href = "/detail/"+ data.workflow_id
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
if (XMLHttpRequest.responseJSON) {
alert(XMLHttpRequest.responseText)
} else {
alert(errorThrown);
}
}
});
}
</script>
<!--group -->
<script>
// 获取实例信息和审批流程
$("#group_name").change(function () {
$.ajax({
type: "post",
url: "/group/instances/",
dataType: "json",
data: {
group_name: $("#group_name").val(),
tag_code: 'can_read'
},
complete: function () {
const urlParams = new URLSearchParams(window.location.search);
const urlSource = urlParams.get('source');
if (urlSource === "resubmit") {
//填充实例信息
$('#instance_name').selectpicker('val', sessionStorage.getItem('editClustername'));
if ($("#instance_name").val()) {
$("#instance_name").selectpicker().trigger("change");
}
}
},
success: function (data) {
if (data.status === 0) {
$("optgroup[id^='optgroup']").empty();
let result = data['data']
const supportDb = [ {% for name in engines.keys %}"{{ name }}", {% endfor %}]
for (let i of result) {
let instance = "<option value=\"" + i.instance_name + "\" instance-id=" + i.id + ">" + i.instance_name + "</option>";
if (supportDb.indexOf(i.db_type) !== -1) {
$("#optgroup-" + i.db_type).append(instance);
}
}
$('#instance_name').selectpicker('render').selectpicker('refresh');
$("#db_name").empty().selectpicker('render').selectpicker('refresh');
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
$.ajax({
type: "post",
url: "/group/auditors/",
dataType: "json",
data: {
group_name: $("#group_name").val(),
workflow_type: 2
},
complete: function () {
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
$("#workflow_auditors").val(result['auditors']);
$("#workflow_auditors_display").text(result['auditors_display']);
$("#div-workflow_auditors").show();
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
});
</script>
<!--init -->
<script>
$(document).ready(function () {
// 清空id, 只允许新增不允许修改
sessionStorage.removeItem('editWorkflowDetailId');
//获取url参数置入对应的初始化内容
const urlParams = new URLSearchParams(window.location.search);
const urlSource = urlParams.get('source');
if (urlSource == "resubmit") {
$("#workflow_name").val(sessionStorage.getItem('editWorkflowNname'));
$("#group_name").val(sessionStorage.getItem('editGroup'));
editor.setValue(sessionStorage.getItem('editSqlContent'));
editor.clearSelection();
} else if (urlSource === "query") {
editor.setValue(sessionStorage.getItem('submitSqlContent'));
editor.clearSelection();
} else {
//重置页面内容
editor.setValue("");
$(".selectpicker").selectpicker('val', '');
$(".selectpicker").selectpicker('render');
$(".selectpicker").selectpicker('refresh');
}
if ($("#group_name").val()) {
$("#group_name").trigger("change");
}
});
</script>
{% endblock %}