{% extends "base.html" %}

{% block content %}
    <!-- 自定义操作按钮-->
    <div id="sqladvisor-toolbar" class="btn-group right" style="display:none;">
        <button id=sqladvisor class="btn btn-default" style="" type="button">获取优化建议</button>
    </div>
    <!-- Nav tabs -->
    <ul id="nav-tabs" class="nav nav-tabs" role="tablist">
        <li id="slowsql_tab" class="active">
            <a href="#slowsql" role="tab" data-toggle="tab">慢日志统计</a>
        </li>
        <li id="slowsqlinfo_tab">
            <a href="#slowsqlinfo" role="tab" data-toggle="tab">慢日志明细</a>
        </li>
        <div class="form-inline pull-right">
            <div class="form-group">
                <select id=instance_name class="form-control selectpicker" name="instances"
                        title="请选择实例:"
                        data-live-search="true">
                </select>
            </div>
            <div class="form-group">
                <select id=db_name class="form-control selectpicker" name="db_name_list"
                        title="请选择数据库:"
                        data-live-search="true">
                </select>
            </div>
            <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>&nbsp;
                    <span></span> <i class="fa fa-caret-down"></i>
                </div>
            </div>
        </div>
    </ul>
    <!-- Tab panes -->
    <div id="tab-content" class="tab-content">
        <!-- 慢日志统计-->
        <div id="slowsql" role="tabpanel" class="tab-pane fade in active table-responsive">
            <table id="slowsql-list" data-toggle="table" class="table table-hover"
                   style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;"></table>

        </div>
        <!-- 慢日志明细-->
        <div id="slowsqlinfo" role="tabpanel" class="tab-pane fade table-responsive">
            <table id="slowsqlinfo-list" data-toggle="table" class="table table-hover"
                   style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;"></table>
        </div>
    </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/export-libs/FileSaver/FileSaver.min.js' %}"></script>
    <script src="{% static 'bootstrap-table/export-libs/js-xlsx/xlsx.core.min.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().startOf('day');
            let end = moment();

            function cb(start, end) {
                $('#reservation span').html(start.format('YYYY-MM-DD') + ' - ' + end.format('YYYY-MM-DD'));
            }

            $('#reservation').daterangepicker({
                startDate: start,
                endDate: end,
                locale: {
                    format: "YYYY-MM-DD",// 显示格式
                    separator: " / ",// 两个日期之间的分割线
                    // 中文化
                    applyLabel: "确定",
                    cancelLabel: "取消",
                    fromLabel: "开始",
                    toLabel: "结束",
                    customRangeLabel: "自定义",
                    daysOfWeek: ["日", "一", "二", "三", "四", "五", "六"],
                    monthNames: ["一月", "二月", "三月", "四月", "五月", "六", "七月", "八月", "九月", "十月", "十一月", "十二月"],
                    firstDay: 1
                },
                ranges: {
                    "今日": [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) {
                sessionStorage.removeItem('SQLId');
                var start_date = picker.startDate.format('YYYY-MM-DD');
                var end_date = picker.endDate.format('YYYY-MM-DD');
                sessionStorage.setItem('start_date', start_date);
                sessionStorage.setItem('end_date', end_date);
                var slowsql_active_li_id = sessionStorage.getItem('slowsql_active_li_id');
                if (slowsql_active_li_id === 'slowsql_tab') {
                    slowquery_review()
                } else if (slowsql_active_li_id === 'slowsqlinfo_tab') {
                    slowquery_review_history()

                }
            });
            cb(start, end);
        });

        //实例变动自动获取数据库
        $("#instance_name").change(function () {
            sessionStorage.setItem('slow_query_instance_name', $("#instance_name").val());
            sessionStorage.removeItem('slow_query_db_name');
            sessionStorage.removeItem('SQLId');
            get_db_list();

        });

        //获取数据库列表,并且获取日志数据
        function get_db_list() {
            //将数据通过ajax提交给获取db_name
            $.ajax({
                type: "get",
                url: "/instance/instance_resource/",
                dataType: "json",
                data: {
                    instance_name: $("#instance_name option:selected").text(),
                    resource_type: "database"
                },
                complete: function () {
                    var slowsql_active_li_id = sessionStorage.getItem('slowsql_active_li_id');
                    if (slowsql_active_li_id === 'slowsql_tab') {
                        slowquery_review()
                    } else if (slowsql_active_li_id === 'slowsqlinfo_tab') {
                        slowquery_review_history()

                    }
                },
                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").prepend("<option value=\"\" selected=\"selected\">全部数据库</option>");
                        $('#db_name').selectpicker('render');
                        $('#db_name').selectpicker('refresh');
                    } else {
                        alert(data.msg);
                    }
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        }

        //数据库变动自动获取日志
        $("#db_name").change(function () {
            sessionStorage.setItem('slow_query_db_name', $("#db_name").val());
            sessionStorage.removeItem('SQLId');
            var slowsql_active_li_id = sessionStorage.getItem('slowsql_active_li_id');
            if (slowsql_active_li_id === 'slowsql_tab') {
                slowquery_review()
            } else if (slowsql_active_li_id === 'slowsqlinfo_tab') {
                slowquery_review_history()

            }
        });

        //保留如果已选择instance_name，进入页面自动加载
        $(document).ready(function () {
            //获取用户实例列表
            $(function () {
                $.ajax({
                    type: "get",
                    url: "/group/user_all_instances/",
                    dataType: "json",
                    data: {
                        db_type: ['mysql']
                    },
                    complete: function () {
                        //保留如果已选择instance_name，进入页面自动加载
                        sessionStorage.removeItem('SQLId');
                        sessionStorage.setItem('slowsql_active_li_id', 'slowsql_tab');
                        if (sessionStorage.getItem('slow_query_instance_name')) {
                            var instance_name = sessionStorage.getItem('slow_query_instance_name');
                            var start_date = sessionStorage.getItem('start_date');
                            var end_date = sessionStorage.getItem('end_date');
                            if (start_date && end_date) {
                                $('#reservation').data('daterangepicker').setStartDate(start_date);
                                $('#reservation').data('daterangepicker').setEndDate(end_date);
                            }
                            $("#instance_name").val(instance_name).trigger("change");
                        }
                    },
                    success: function (data) {
                        if (data.status === 0) {
                            let result = data['data'];
                            $("#instance_name").empty();
                            $("#target_instance_name").empty();
                            for (let i = 0; i < result.length; i++) {
                                let instance = "<option value=\"" + result[i]['instance_name'] + "\">" + result[i]['instance_name'] + "</option>";
                                $("#instance_name").append(instance);
                                $("#target_instance_name").append(instance);
                            }
                            $('#instance_name').selectpicker('render');
                            $('#instance_name').selectpicker('refresh');
                            $('#target_instance_name').selectpicker('render');
                            $('#target_instance_name').selectpicker('refresh');
                        } else {
                            alert(data.msg);
                        }
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        alert(errorThrown);
                    }
                });
            });
        });

        //tab切换,保留当前激活的标签id
        $(function () {
            $("#nav-tabs").on('shown.bs.tab', "li", function (e) {
                var active_li_id = $(e.target).parents().attr('id');
                sessionStorage.setItem('slowsql_active_li_id', active_li_id);
                //当前激活的标签id
                if ($("#instance_name").val()) {
                    if (active_li_id === 'slowsql_tab') {
                        sessionStorage.removeItem('SQLId');
                        slowquery_review();
                    } else if (active_li_id === 'slowsqlinfo_tab') {
                        slowquery_review_history()
                    }
                }
            });
        });
    </script>
    <!-- sqladvisor -->
    <script>
        //点击sql统计跳转到对应sql列表
        $(function () {
            $('#slowsql-list').on('click-row.bs.table', function (e, row, element) {
                sessionStorage.setItem('SQLId', row.SQLId);
                $('#nav-tabs a[href="#slowsqlinfo"]').tab('show')
            });
        });

        //点击优化按钮获取优化信息
        $(function () {
            $("#sqladvisor").click(function () {
                if ($("#slowsqlinfo-list").bootstrapTable('getSelections')[0]) {
                    var advisor_sql_content = $("#slowsqlinfo-list").bootstrapTable('getSelections')[0]['SQLText'] + ';';
                    var advisor_db_name = $("#slowsqlinfo-list").bootstrapTable('getSelections')[0]['DBName'];
                    var advisor_instance_name = $("#instance_name").val();
                    sessionStorage.setItem('advisor_sql_content', advisor_sql_content);
                    sessionStorage.setItem('advisor_db_name', advisor_db_name);
                    sessionStorage.setItem('advisor_instance_name', advisor_instance_name);
                    location.href = '/slowquery_advisor/';
                } else {
                    alert("请先选择需要优化的sql")
                }
            });
        });
    </script>
    <!-- 获取列表数据 -->
    <script>
        // 获取慢日志统计
        function slowquery_review() {
            $("#sqladvisor-toolbar").hide();
            var instance_name = $("#instance_name").val();
            if (instance_name) {
                //初始化table
                $('#slowsql-list').bootstrapTable('destroy').bootstrapTable({
                    escape: true,
                    method: 'post',
                    contentType: "application/x-www-form-urlencoded",
                    url: "/slowquery/review/",
                    striped: true,                      //是否显示行间隔色
                    cache: false,                       //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
                    pagination: true,                   //是否显示分页（*）
                    sortable: true,                     //开启排序
                    sortName: "MySQLTotalExecutionCounts", //默认排序字段名称
                    sortOrder: "desc",                   //默认排序方式
                    sidePagination: "server",           //分页方式：client客户端分页，server服务端分页（*）
                    pageNumber: 1,                      //初始化加载第一页，默认第一页,并记录
                    pageSize: 30,                     //每页的记录行数（*）
                    pageList: [30, 50, 100, 1000],       //可供选择的每页的行数（*）
                    search: true,                      //是否显示表格搜索
                    strictSearch: false,                //是否全匹配搜索
                    showColumns: true,                  //是否显示所有的列（选择显示的列）
                    showRefresh: true,                  //是否显示刷新按钮
                    showExport: true,                   //是否显示导出按钮
                    exportTypes: ['json', 'sql', 'csv', 'txt', 'xml', 'xlsx'],
                    exportOptions: {
                        ignoreColumn: [0],  //忽略某些列的索引数组
                        fileName: 'slowquery_review'  //文件名称设置
                    },
                    minimumCountColumns: 2,             //最少允许的列数
                    clickToSelect: true,                //是否启用点击选中行
                    uniqueId: "id",                     //每一行的唯一标识，一般为主键列
                    showToggle: true,                   //是否显示详细视图和列表视图的切换按钮
                    cardView: false,                    //是否显示详细视图
                    detailView: true,                  //是否显示父子表
                    queryParamsType: 'limit',
                    queryParams: function (params) {
                        var instance_name = $("#instance_name").val();
                        var db_name = $("#db_name").val();
                        var StartTime = $('#reservation').data('daterangepicker').startDate.format('YYYY-MM-DD');
                        var EndTime = $("#reservation").data('daterangepicker').endDate.format('YYYY-MM-DD');
                        //console.info(params);
                        //console.info('params='+JSON.stringify(params));

                        return {
                            instance_name: instance_name,
                            db_name: db_name,
                            StartTime: StartTime,
                            EndTime: EndTime,
                            limit: params.limit,
                            offset: params.offset,
                            search: params.search,
                            sortName: params.sort,
                            sortOrder: params.order
                        }
                    },
                    //格式化详情
                    detailFormatter: function (index, row) {
                        let html = [];
                        $.each(row, function (key, value) {
                            if (key === 'SQLText') {
                                let sql = window.sqlFormatter.format(value);
                                //替换所有的换行符
                                sql = sql.replace(/\r\n/g, "<br>");
                                sql = sql.replace(/\n/g, "<br>");
                                //替换所有的空格
                                sql = sql.replace(/\s/g, "&nbsp;");
                                html.push('<div class="col-lg-4">' + sql + '</div>');
                            }
                        });
                        $.ajax({
                            type: "get",
                            url: "/slowquery/report/",
                            async: false,
                            dataType: "json",
                            data: {
                                checksum: row.SQLId,
                            },
                            complete: function () {
                            },
                            success: function (data) {
                                if (data.status === 0) {
                                    let chart = data.data;
                                    html.push('<div class="col-lg-8">' + chart + '</div>')
                                }
                            },
                            error: function (XMLHttpRequest, textStatus, errorThrown) {
                                alert(errorThrown);
                            }
                        });
                        return html.join('');
                    },
                    columns: [{
                        title: '日志统计时间',
                        field: 'CreateTime',
                        sortable:true
                    }, {
                        title: '数据库名',
                        field: 'DBName',
                        sortable:true
                    }, {
                        title: 'SQL语句',
                        field: 'SQLText',
                        cellStyle:      //突出sql统计信息
                            function formatTableUnit(value, row, index) {
                                return {
                                    css: {
                                        "color": '#06C',
                                        "text-decoration": 'underline',
                                        "cursor": 'pointer'
                                    }
                                }
                            },
                        formatter: function (value, row, index) {
                            if (value.length > 100) {
                                var sql = value.substr(0, 100) + '...';
                                return sql;
                            } else {
                                return value
                            }
                        }
                    }, {
                        title: '完整SQL语句',
                        field: 'SQLText',
                        visible: false // 默认不显示
                    }, {
                        title: '执行总次数',
                        field: 'MySQLTotalExecutionCounts',
                        sortable:true
                    }, {
                        title: '执行总时长(秒)',
                        field: 'MySQLTotalExecutionTimes',
                        sortable:true
                    }, {
                        title: '平均执行时长(秒)',
                        field: 'QueryTimeAvg',
                        sortable:true
                    }, {
                        title: '扫描总行数',
                        field: 'ParseTotalRowCounts',
                        sortable:true
                    }, {
                        title: '返回总行数',
                        field: 'ReturnTotalRowCounts',
                        sortable:true
                    }, {
                        title: '平均扫描行数',
                        field: 'ParseRowAvg',
                        sortable:true
                    }, {
                        title: '平均返回行数',
                        field: 'ReturnRowAvg',
                        sortable:true
                    }],
                    locale: 'zh-CN',
                    onLoadError: onLoadErrorCallback,
                    onSearch: function(e) {
                        //传搜索参数给服务器
                        //console.info('search='+e);
                        //queryParams(e);
                    },
                    responseHandler: function(res) {
                        //在ajax获取到数据，渲染表格之前，修改数据源
                        return res;
                    },onSort: function(sortName,order){
                        //console.info('onSort: name='+sortName+'; order='+order+';');
                    }
                });
            } else {
                alert('请选择实例')
            }
        }

        // 获取慢日志明细
        function slowquery_review_history() {
            $("#sqladvisor-toolbar").show();
            var instance_name = $("#instance_name").val();
            if (instance_name) {
                //初始化table
                $('#slowsqlinfo-list').bootstrapTable('destroy').bootstrapTable({
                    escape: true,
                    method: 'post',
                    contentType: "application/x-www-form-urlencoded",
                    url: "/slowquery/review_history/",
                    striped: true,                      //是否显示行间隔色
                    cache: false,                       //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
                    pagination: true,                   //是否显示分页（*）
                    sortable: true,                     //开启排序
                    sortName: "ParseRowCounts",         //默认排序字段名称
                    sortOrder: "desc",                   //默认排序方式
                    sidePagination: "server",           //分页方式：client客户端分页，server服务端分页（*）
                    pageNumber: 1,                      //初始化加载第一页，默认第一页,并记录
                    pageSize: 30,                     //每页的记录行数（*）
                    pageList: [30, 50, 100, 1000],       //可供选择的每页的行数（*）
                    search: true,                      //是否显示表格搜索
                    strictSearch: false,                //是否全匹配搜索
                    showColumns: true,                  //是否显示所有的列（选择显示的列）
                    showRefresh: true,                  //是否显示刷新按钮
                    showExport: true,                   //是否显示导出按钮
                    exportTypes: ['json', 'sql', 'csv', 'txt', 'xml', 'xlsx'],
                    exportOptions: {
                        ignoreColumn: [0],  //忽略某些列的索引数组
                        fileName: 'slowquery_review_history'  //文件名称设置
                    },
                    minimumCountColumns: 2,             //最少允许的列数
                    clickToSelect: true,                //是否启用点击选中行
                    uniqueId: "id",                     //每一行的唯一标识，一般为主键列
                    showToggle: true,                   //是否显示详细视图和列表视图的切换按钮
                    cardView: false,                    //是否显示详细视图
                    detailView: true,                  //是否显示父子表
                    queryParamsType: 'limit',
                    queryParams: function (params) {
                        var instance_name = $("#instance_name").val();
                        var db_name = $("#db_name").val();
                        var SQLId = sessionStorage.getItem('SQLId');
                        var StartTime = $('#reservation').data('daterangepicker').startDate.format('YYYY-MM-DD');
                        var EndTime = $("#reservation").data('daterangepicker').endDate.format('YYYY-MM-DD');
                        //console.info(params);
                        //console.info('params='+JSON.stringify(params));

                        return {
                            instance_name: instance_name,
                            db_name: db_name,
                            SQLId: SQLId,
                            StartTime: StartTime,
                            EndTime: EndTime,
                            limit: params.limit,
                            offset: params.offset,
                            search: params.search,
                            sortName: params.sort,
                            sortOrder: params.order
                        }
                    },
                    //格式化详情
                    detailFormatter: function (index, row) {
                        var html = [];
                        $.each(row, function (key, value) {
                            if (key === 'SQLText') {
                                var sql = window.sqlFormatter.format(value);
                                //替换所有的换行符
                                sql = sql.replace(/\r\n/g, "<br>");
                                sql = sql.replace(/\n/g, "<br>");
                                //替换所有的空格
                                sql = sql.replace(/\s/g, "&nbsp;");
                                html.push('<span>' + sql + '</span>');
                            }
                        });
                        return html.join('');
                    },
                    columns: [{
                        title: '',
                        field: 'checkbox',
                        checkbox: true
                    }, {
                        title: '执行开始时间',
                        field: 'ExecutionStartTime',
                        sortable:true
                    }, {
                        title: '数据库名',
                        field: 'DBName',
                        sortable:true
                    }, {
                        title: '用户名',
                        field: 'HostAddress',
                        sortable:true
                    }, {
                        title: 'SQL语句',
                        field: 'SQLText',
                        formatter: function (value, row, index) {
                            if (value.length > 100) {
                                var sql = value.substr(0, 100) + '...';
                                return sql;
                            } else {
                                return value
                            }
                        }
                    }, {
                        title: '完整SQL语句',
                        field: 'SQLText',
                        visible: false // 默认不显示
                    }, {
                        title: '执行总次数',
                        field: 'TotalExecutionCounts',
                        sortable:true
                    }, {
                        title: '执行时长(95%)',
                        field: 'QueryTimePct95',
                        sortable:true
                    }, {
                        title: '执行总时长(秒)',
                        field: 'QueryTimes',
                        sortable:true
                    }, {
                        title: '锁定总时长(秒)',
                        field: 'LockTimes',
                        sortable:true
                    }, {
                        title: '解析总行数',
                        field: 'ParseRowCounts',
                        sortable:true
                    }, {
                        title: '返回总行数',
                        field: 'ReturnRowCounts',
                        sortable:true
                    }],
                    singleSelect: true,
                    toolbar: "#sqladvisor-toolbar", //指明自定义的toolbar
                    locale: 'zh-CN',
                    onLoadError: onLoadErrorCallback,
                    onSearch: function (e) {
                        //传搜索参数给服务器
                        queryParams(e)
                    },onSort: function(sortName,order){
                        //console.info('onSort: name='+sortName+'; order='+order+';');
                    },
                    responseHandler: function (res) {
                        //在ajax获取到数据，渲染表格之前，修改数据源
                        return res;
                    }
                });
            } else {
                alert("请选择实例")
            }
        }
    </script>
{% endblock %}

