{% extends "base.html" %}
{% block content %}
<div id="toolbar" class="form-inline pull-left">
<div class='form-group'>
<div id="reservation" class="form-control"
style="background: #fff; cursor: pointer; padding: 5px 10px; border: 1px solid #ccc; width: 100%">
<i class="fa fa-calendar"></i>
<span></span> <i class="fa fa-caret-down"></i>
</div>
</div>
</div>
<!-- 表格-->
<div class="table-responsive">
<table id="sql-log" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
{% endblock content %}
{% block js %}
{% load static %}
<link href="{% static 'daterangepicker/css/daterangepicker.css' %}" rel="stylesheet" type="text/css"/>
<script src="{% static 'daterangepicker/js/moment.min.js' %}"></script>
<script src="{% static 'daterangepicker/js/daterangepicker.js' %}"></script>
<script src="{% static 'bootstrap-table/js/bootstrap-table-export.min.js' %}"></script>
<script src="{% static 'bootstrap-table/js/tableExport.min.js' %}"></script>
<script>
// 初始化时间控件
$(function () {
let start = moment().subtract(6, 'days');
let end = moment();
function cb(start, end) {
if (start.isValid() && end.isValid()) {
$('#reservation span').html(start.format('YYYY-MM-DD') + ' - ' + end.format('YYYY-MM-DD'));
} else {
$('#reservation span').html('全部数据');
}
}
$('#reservation').daterangepicker({
startDate: start,
endDate: end,
showDropdowns: true,
locale: {
format: "YYYY-MM-DD",// 显示格式
separator: " / ",// 两个日期之间的分割线
// 中文化
applyLabel: "确定",
cancelLabel: "取消",
fromLabel: "开始",
toLabel: "结束",
customRangeLabel: "自定义",
daysOfWeek: ["日", "一", "二", "三", "四", "五", "六"],
monthNames: ["一月", "二月", "三月", "四月", "五月", "六", "七月", "八月", "九月", "十月", "十一月", "十二月"],
firstDay: 1
},
ranges: {
"全部": [null, null],
"今日": [moment().startOf('day'), moment()],
"昨日": [moment().subtract('days', 1).startOf('day'), moment().subtract('days', 1).endOf('day')],
"最近7日": [moment().subtract('days', 6), moment()],
"最近30日": [moment().subtract('days', 29), moment()],
"本月": [moment().startOf("month"), moment().endOf("month")],
"上个月": [moment().subtract(1, "month").startOf("month"), moment().subtract(1, "month").endOf("month")]
}
}, cb).on('apply.daterangepicker', function (ev, picker) {
querylist()
});
cb(start, end);
});
</script>
<script>
//获取列表
function querylist() {
//初始化table
$('#sql-log').bootstrapTable('destroy').bootstrapTable({
escape: true,
method: 'get',
contentType: "application/x-www-form-urlencoded",
url: "/query/querylog_audit/",
striped: true, //是否显示行间隔色
cache: false, //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
pagination: true, //是否显示分页（*）
sortable: true, //是否启用排序
sortOrder: "asc", //排序方式
sidePagination: "server", //分页方式：client客户端分页，server服务端分页（*）
pageNumber: 1, //初始化加载第一页，默认第一页,并记录
pageSize: 20, //每页的记录行数（*）
pageList: [10, 30, 50, 100, 500, 1000], //可供选择的每页的行数（*）
showExport: true, //是否显示导出按钮
exportTypes: ['json', 'sql', 'csv', 'txt', 'xml', 'xlsx'],
exportDataType: "all",
exportOptions: {
ignoreColumn: [0], //忽略某些列的索引数组
fileName: "query_log_" + dateFormat("yyyyMMddhhmmss",new Date())
},
search: true, //是否显示表格搜索
strictSearch: false, //是否全匹配搜索
showColumns: true, //是否显示所有的列（选择显示的列）
showRefresh: true, //是否显示刷新按钮
minimumCountColumns: 2, //最少允许的列数
clickToSelect: true, //是否启用点击选中行
uniqueId: "id", //每一行的唯一标识，一般为主键列
showToggle: true, //是否显示详细视图和列表视图的切换按钮
cardView: false, //是否显示详细视图
detailView: true, //是否显示父子表
locale: 'zh-CN', //本地化
toolbar: "#toolbar", //指明自定义的toolbar
queryParamsType: 'limit',
//获取查询列表请求服务数据时所传参数
queryParams:
function (params) {
let start_date = $("#reservation").data('daterangepicker').startDate;
let end_date = $("#reservation").data('daterangepicker').endDate;
if (start_date.isValid() && end_date.isValid()) {
start_date = start_date.format('YYYY-MM-DD');
end_date = end_date.format('YYYY-MM-DD')
} else {
start_date = '';
end_date = ''
}
return {
star: '',
query_log_id: '',
limit: params.limit,
offset: params.offset,
search: params.search,
start_date: start_date,
end_date: end_date
}
},
//格式化详情
detailFormatter: function (index, row) {
var html = [];
$.each(row, function (key, value) {
if (key === 'sqllog') {
//替换标签
value = value.replace(/&/g, "&");
value = value.replace(/</g, "<");
value = value.replace(/>/g, ">");
value = value.replace(/"/g, """);
//替换所有的换行符
value = value.replace(/\r\n/g, "<br>");
value = value.replace(/\n/g, "<br>");
//替换所有的空格
value = value.replace(/\s/g, " ");
html.push('<span>' + value + '</span>');
}
});
return html.join('');
},
columns: [{
title: '用户',
field: 'user_display'
}, {
title: '实例',
field: 'instance_name'
}, {
title: '数据库',
field: 'db_name'
}, {
title: '查询时间',
field: 'create_time'
}, {
title: '语句',
field: 'sqllog',
formatter: function (value, row, index) {
var _sql=row.sqllog
if (_sql.length > 50) {
var sql = _sql.substr(0, 50) + '...';
return sql;
} else {
return value
}
}
}, {
title: '完整语句',
field: 'sqllog',
visible: false // 默认不显示
}, {
title: '返回行数',
field: 'effect_row'
}, {
title: '耗时(秒)',
field: 'cost_time'
}],
onLoadSuccess: function () {
},
onLoadError: onLoadErrorCallback,
onSearch: function (e) {
//传搜索参数给服务器
queryParams(e)
},
responseHandler: function (res) {
//在ajax获取到数据，渲染表格之前，修改数据源
return res;
}
});
}
//初始化数据
$(document).ready(function () {
querylist();
});
</script>
{% endblock %}